Stored procedure can not be called due to syntax error

We migrated from SQL Server to Postgres and I am trying to rewrite a stored procedure. The procedure is created correctly, but I can not call it.

This is my procedure:

CREATE OR REPLACE PROCEDURE spr_getItems  ( 
  p_kind int = NULL,
  p_customerId varchar(256) = NULL,
  p_resourceIds varchar(2048) = NULL,
  p_referenceIds varchar(2048) = NULL
)
AS $$
BEGIN
  SELECT
    c.kind,
    c.name AS customerName,
    c.oid AS customerId,
    r.name AS resourceName,
    r.oid AS resourceId
    o.fullObject AS fullObjectString
  FROM m_customer c
  JOIN m_resource r
    ON r.oid = c.resourceOid
  LEFT JOIN m_object o
    ON o.customerOid = c.oid
      AND o.customerOid = p_customerId
  WHERE (c.kind = p_kind OR p_kind is NULL)
    AND (c.referenceOid IN (SELECT refTemp.oid FROM tvf_commaSeperatedStringToTable(p_referenceIds) refTemp) OR p_referenceIds is NULL)
    AND (r.oid IN (SELECT resTemp.oid FROM tvf_commaSeperatedStringToTable(p_resourceIds) resTemp) OR p_resourceIds is NULL); 
END;
$$

LANGUAGE 'plpgsql';

the table-valued-function tvf_commaSeperatedStringToTable just takes a string, splits it and returns a table with all of the different ids and a rownumber. It works just fine and is tested, no errors inside here.

Now when I try to execute it like this

CALL public.spr_getItems (0, null, null, null)

I get this output:

ERROR: query has no destination for result data

HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function spr_getItems(integer,character varying,character varying,character varying) line 3 at SQL statement
SQL state: 42601

But I do NOT want to discard the result, I want to see them.

So I tried calling it with select

SELECT * 
FROM CALL spr_getItems (0, null, null, null)

and then I get this syntax error:

ERROR: syntax error at or near “0”
LINE 2: 0,
^
SQL state: 42601
Character: 40

I also tried executing it in several other way eg by adding the “public.” before the procedures name, but then there has been a syntax error at the “.”. Or with just using select spr_getItems(0, null, null, null) or select spr_getItems(0), select * from call spr_getItems (0) and so on and so forth.

Am I doing something completely wrong and overlooked something in the documentation?

Thanks for any help!

Edit: clarification that I want to see the results
Edit2: accidentally copied a wrong function name
Edit3: added complete body as suggested

Answer

That’s not how Postgres works. Procedures aren’t meant to return result sets.

If you want that use a set returning function:

CREATE OR REPLACE function spr_getItems  ( 
  p_kind int = NULL,
  p_customerId varchar(256) = NULL,
  p_resourceIds varchar(2048) = NULL,
  p_referenceIds varchar(2048) = NULL
)
  returns table (kind text, customername text, customerid integer, resourcename text, resourceid integer, fullobjectstring text)
AS $$
  SELECT
    c.kind,
    c.name AS customerName,
    c.oid AS customerId,
    r.name AS resourceName,
    r.oid AS resourceId
    o.fullObject AS fullObjectString
  FROM m_customer c
  JOIN m_resource r
    ON r.oid = c.resourceOid
  LEFT JOIN m_object o
    ON o.customerOid = c.oid
      AND o.customerOid = p_customerId
  WHERE (c.kind = p_kind OR p_kind is NULL)
    AND (c.referenceOid IN (SELECT refTemp.oid FROM tvf_commaSeperatedStringToTable(p_referenceIds) refTemp) OR p_referenceIds is NULL)
    AND (r.oid IN (SELECT resTemp.oid FROM tvf_commaSeperatedStringToTable(p_resourceIds) resTemp) OR p_resourceIds is NULL); 
$$
LANGUAGE sql;

You also don’t need PL/pgSQL for a simple query encapsulation, language sql will do just fine.

Then use it like a table:

select *
from spr_getitems(....);

Note that I guessed the data types in the returns table (...) part, you will have to adjust that to the real types used in your tables.

---

You don’t need the sub-selects to handle the comma separated values either.

E.g. this:

AND (c.referenceOid IN (SELECT refTemp.oid FROM tvf_commaSeperatedStringToTable(p_referenceIds) refTemp) OR p_referenceIds is NULL)

can be simplified to

AND (c.referenceOid = any (string_to_array(p_referenceIds, ',')  OR p_referenceIds is NULL)

But passing multiple values as a comma separated string is bad coding style to begin with. You should declare those parameters as array and pass proper arrays to the function.