PL/PgSql Dynamic select query not returning data

Question

Creating a function to return some data from an existent table. Im constructing the schema.table for the select query dynamically based on function parameters passed on run time but when calling the function it returns nothing.

Code below

DROP TYPE IF EXISTS metadata.RETURNED_DATA CASCADE;

CREATE TYPE metadata.RETURNED_DATA AS
(
    postal_id          BIGINT,
    postal_code        VARCHAR(15),
    admin_id   BIGINT,
    admin_code VARCHAR(11)
);

DROP FUNCTION IF EXISTS metadata.pc_get_data(VARCHAR, VARCHAR, VARCHAR);

CREATE OR REPLACE FUNCTION metadata.pc_get_data(reg CHARACTER VARYING, iso CHARACTER VARYING,
                                                        pcode CHARACTER VARYING
) RETURNS SETOF metadata.RETURNED_DATA

    LANGUAGE plpgsql
AS
$$

BEGIN

    EXECUTE FORMAT(
            'SELECT postal_id,
                   postal_code,
                   admin_id,
                   admin_code
            FROM %I_content.%I_pc_aet_data
            WHERE postal_code = %L', reg, iso, pcode);

END

$$;

SELECT *
FROM pc_get_data('aaa', 'bbb', '12345');

It doesn't return anything because you don't RETURN anything. You need to `RETURN QUERY EXECUTE` if you want to return the query results. — jjanes, Mar 13 '22 at 15:51
that was it i was missing the 'RETURN QUERY' part , thanks its now working. regards — Jorge Vidinha, Mar 15 '22 at 14:12

score 0 · Answer 1 · answered Mar 13 '22 at 12:20

I'm not familiar with this syntax and have no means to test it right now, so, if my answer is mistaken, let me know and I will edit/remove it.

According to the docs, you can do a select into: https://www.postgresql.org/docs/9.1/plpgsql-statements.html

Example:

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

Maybe you can do the following (untested):


DROP FUNCTION IF EXISTS metadata.pc_get_data(VARCHAR, VARCHAR, VARCHAR);

CREATE OR REPLACE FUNCTION metadata.pc_get_data(reg CHARACTER VARYING, iso CHARACTER VARYING,
                                                        pcode CHARACTER VARYING
) RETURNS SETOF metadata.RETURNED_DATA

    LANGUAGE plpgsql
AS
$$

BEGIN

    EXECUTE FORMAT(
            'SELECT postal_id,
                   postal_code,
                   admin_id,
                   admin_code
            INTO TEMP TABLE my_temp
            FROM %I_content.%I_pc_aet_data
            WHERE postal_code = %L', reg, iso, pcode);

            `SELECT * FROM my_temp;

END

$$;

score 0 · Answer 2 · answered Mar 13 '22 at 13:07

0

You can't return a value from EXECUTE unless you use SELECT...INTO.

Good examples can be found in PostgreSQL wiki

answered Mar 13 '22 at 13:07

Vesa Karjalainen

1,087
8
15

score 0 · Answer 3 · answered Mar 14 '22 at 09:08

try to use text data type.
--https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_varchar.28n.29_by_default

CREATE TYPE metadata.RETURNED_DATA AS
(
    postal_id          BIGINT,
    postal_code        text,
    admin_id   BIGINT,
    admin_code text
);

 CREATE OR REPLACE FUNCTION metadata.pc_get_data(reg text, iso text, pcode text
    ) RETURNS SETOF aaa_content.RETURNED_DATA
        LANGUAGE plpgsql
    AS
    $$
    BEGIN
    return query execute FORMAT(
    'SELECT postal_id,
    postal_code,
    admin_id,
    admin_code
    FROM %I_content.%I_pc_aet_data
    WHERE postal_code = %L', reg, iso, pcode);
    END
    $$;

Be careful with composite type: Invalid input syntax for type integer: "(2,2)" with composite data type while executing function

score 0 · Accepted Answer · answered Mar 15 '22 at 14:18

Adding the 'RETURN QUERY EXECUTE' as suggested by @jjanes on is comment solved the missing part.

Bellow the final code snippet.

DROP FUNCTION IF EXISTS metadata.pc_get_data(VARCHAR, VARCHAR, VARCHAR);

CREATE OR REPLACE FUNCTION metadata.pc_get_data(reg CHARACTER VARYING, iso CHARACTER VARYING,
                                                        pcode CHARACTER VARYING
) RETURNS SETOF metadata.RETURNED_DATA

    LANGUAGE plpgsql
AS
$$

BEGIN
     RETURN QUERY -- ## the missing part ## 

    EXECUTE FORMAT(
            'SELECT postal_id,
                   postal_code,
                   admin_id,
                   admin_code
            INTO TEMP TABLE my_temp
            FROM %I_content.%I_pc_aet_data
            WHERE postal_code = %L', reg, iso, pcode);

            `SELECT * FROM my_temp;

END

$$;

PL/PgSql Dynamic select query not returning data

4 Answers4