Procedure returns query table in Postgresql

Question

I try to create a procedure that return a query table from Postgresql, but I'm stuck at creating the procedure

CREATE OR REPLACE PROCEDURE "sap_data"."get_emp_number"(dep VARCHAR)
     RETURNS TEMP TABLE (
                    emp_id VARCHAR,
                    department VARCHAR)
     AS $BODY$
     BEGIN
        RETURN QUERY SELECT
            emp_id,
            department
        FROM
            emp_data
        WHERE
            department = dep
    END;$BODY$
      LANGUAGE plpgsql

When I save this procedure, this error occurs:

ERROR: syntax error at or near "TABLE" LINE 2: RETURNS TABLE (

I want to return this as a query so I can query this table using parameter to filter it.

Did you copy & paste your code? Because there's no space between the quotation mark and the bracket: "get_emp_number"(dep VARCHAR) — Alan
– Alan, Commented Nov 1, 2021 at 13:30

Stefanov.sm · Accepted Answer · 2021-11-01 18:58:52Z

3

There are several errors in the creation.

It is function.
Functions can't return a temporal table.
You should prefix columns with a table name in RETURN QUERY SELECT.
The semicolon before END is needed.

it might look something like this:

CREATE OR REPLACE Function "get_emp_number"(dep VARCHAR)
     RETURNS TABLE (
                    emp_id VARCHAR,
                    department VARCHAR)
     AS $BODY$
     BEGIN
        RETURN QUERY SELECT
            emp_data.emp_id,
            emp_data.department
        FROM
            emp_data
        WHERE
            emp_data.department = dep;
    END;
    $BODY$
    LANGUAGE plpgsql

edited Nov 1, 2021 at 18:58

Stefanov.sm

13.3k2 gold badges25 silver badges24 bronze badges

answered Nov 1, 2021 at 14:09

Anton Grig

1,71910 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

Procedure returns query table in Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related