0

I have a very simple stored procedure:

CREATE OR REPLACE PROCEDURE delivery.get_assets(IN row_limit integer) 
AS $$
BEGIN
    select group_id 
    from delivery.assets
    order by id desc 
    limit row_limit;
END ;
$$
LANGUAGE plpgsql ;

I'm trying to run the SP using: 

CALL delivery.get_assets(6)

The error I get is as follows:

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 delivery.get_assets(integer) line 3 at SQL statement SQL state: 42601

I'm using PostgresQL 11.

In SQL Server all I have to do is call the SP. I read that in Postgres, you don't need a return statement as you would with functions. Any help is appreciated!

***AS PER COMMENTS BELOW, I'VE CHANGED THE SP TO A FUNCTION (Thank you JSpratt & Horse With No Name).

I'm almost there, but now when I call the function, it's returning in the following format:

returned data

How do I get this to return as a normal data set in a grid?

CREATE OR REPLACE FUNCTION delivery.get_assets(
    number_rows integer)
    RETURNS TABLE(assets_media_id INT, assets_person_id INT) 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
BEGIN
 RETURN QUERY SELECT
 media_id,
 person_id  
 FROM delivery.assets;

END; $BODY$;
kickinchicken
  • 1,281
  • 4
  • 18
  • 40
  • Use `PERFORM` instead of `SELECT` in your query. Why have a procedure to select something without returning the results though? If you want the results you need to add `RETURNS ` where is a `TABLE` or the type of `group_id` and add `RETURNS QUERY` above your query. – J Spratt Mar 20 '19 at 17:13
  • 2
    The `RETURNS` statement is added before `AS $$`. What you're trying to do is actually a `FUNCTION`. – J Spratt Mar 20 '19 at 17:20
  • @JSpratt: I'm working off of this document: https://severalnines.com/blog/overview-new-stored-procedures-postgresql-11 It looks like you don't have to return anything - that the SP does that on its own. To your second point. I tried adding the Return before AS $$ and it didn't work. – kickinchicken Mar 20 '19 at 17:32
  • OK... I think I misunderstood. SP's don't return anything. So they're more for Inserts and updates? – kickinchicken Mar 20 '19 at 17:34
  • 1
    If you want to return a result, use a set returning function not a procedure. If you don't want to return anything, you need to store the result somewhere. –  Mar 20 '19 at 18:54
  • There is no need for PL/pgSQL to wrap a simple SELECT statement. A `language sql` would be much more efficient –  Mar 21 '19 at 13:44
  • Thanks @a_horse_with_no_name. I've tried using the SQL language, but I get the following error: "ERROR: syntax error at or near "RETURN". LINE 16: RETURN QUERY SELECT". I'm coming from SQL Server where creating functions/stored procedures seems to be a bit more straightforward. I appreciate the help! – kickinchicken Mar 21 '19 at 14:05
  • 1
    `return` is a PL/pgSQL statement. See here for an SQL example: https://rextester.com/WRIW16688 –  Mar 21 '19 at 14:22

0 Answers0