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
CALL
is used with procedures.SELECT
is used with functions. – Jeremy