Stored procedure can not be called due to syntax error

Question

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

The two answers so far have given you some good leads on how to fix this, but it seems you may also be using a procedure where you should be using a function. If you want results from a stored bit of code, use a function. CALL is used with procedures. SELECT is used with functions. — Jeremy
– Jeremy, Commented Jan 6, 2020 at 17:46
@Jeremy As i said in my question, i only used mssql so far. There i used stored procedures to get one or more recordsets that i can use in my frontend. I will try use a function instead of a procedure and see what the results are. Afterwards i will go and lookup the difference between functions and stored procedures in postgres to get a better understanding of when to use what.Thank you — 404_Name_Not_Found
– 404_Name_Not_Found, Commented Jan 7, 2020 at 7:52

user330315 · Accepted Answer · 2020-01-07 09:29:58Z

2

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.

answered Jan 7, 2020 at 9:29

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

404_Name_Not_Found Over a year ago

Thanks a lot, this works. But i would like to just call the procedure in my node js application like i did using the mssql library. i could just create a ConnectionPool there, start a request and give some input parameters. Then i just run execute on the procedure and get the results. Guess i will have to exchange that library anyway but just calling a procedure seems more convenient to me than to create select scripts and run those, even if they are very small. Again, thanks! Found an article to lookup the difference of stored procedures and functions in postgres.

user330315 Over a year ago

You will have to change your calling code. Selecting from the function is the same as selecting from a table - why is running a SELECT so "inconvenient" in that library? Does it not support running SELECT statements?

404_Name_Not_Found Over a year ago

This library is only for mssql. i will use a postgres library that is similar to it (pg or postgres, i think. not sure yet). both support running SELECT statements. i just meant that i have been able to use something like that:

pool.request().input('param1', value1).input('param2', value2).execute('spr_name' (err, result) => { /* do something */ }

which in my opinion is very readable. using the pg library the syntax would now be like pool.query('SELECT * FROM spr_name ($1, $2)', [value1,value2], (err, res) => { /* do something */ }). Seems like a little bit less code but also less readable

juan_carlos_yl · Accepted Answer · 2020-01-06 16:33:54Z

0

The error refers to a function call (spr_getshadowrefs) inside the public.spr_getItems procedure. Perhaps you're trying to execute the spr_getshadowrefs function without putting the result in any variable.
Try to use PERFORM when you execute the spr_getshadowrefs function inside the public.spr_getItems procedure.

edited Jan 6, 2020 at 16:33

answered Jan 6, 2020 at 16:15

juan_carlos_yl

74010 silver badges15 bronze badges

4 Comments

Abelisto Over a year ago

He should not to "use PERFORM when you execute that function" but use PERFORM at line 3 of that function's body BTW.

Abelisto Over a year ago

"CONTEXT: PL/pgSQL function spr_getshadowrefs(integer,character varying,character varying,character varying) line 3 at SQL statement" means that error occurred inside function spr_getshadowrefs, not while the calling it.

juan_carlos_yl Over a year ago

Oh you're right! So, is there a SELECT statement inside spr_getshadowrefs function that doesn't give a value to any variable? If would be helpful if he could provide us some code of the procedure.<br> PD: use CALL public.spr_getItems ( 0, null, null, null)

404_Name_Not_Found Over a year ago

@JuanCarlosYupanquiLozano Sorry i accidentally copied a wrong function name that has been open in another query, same error though. i do not call a function inside of a function. My bad but thank you for trying to help!

mrc85 · Accepted Answer · 2020-01-07 14:48:59Z

0

Have you tried

EXEC spr_getItems p_kind = 0,
  p_customerId = NULL,
  p_resourceIds = NULL,
  p_referenceIds = NULL

edited Jan 7, 2020 at 14:48

answered Jan 6, 2020 at 15:57

mrc85

991 silver badge11 bronze badges

2 Comments

404_Name_Not_Found Over a year ago

just tried it... ERROR: syntax error at or near "EXEC" LINE 1: EXEC spr_getItems (0, null, null, null); ^ SQL state: 42601 Character: 1

user330315 Over a year ago

There is no exec in Postgres

Collectives™ on Stack Overflow

Stored procedure can not be called due to syntax error

3 Answers 3

3 Comments

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related