PL/PgSql Dynamic select query not returning data-CodePudding

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');

CodePudding user response：

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

$$;

CodePudding user response：

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

Good examples can be found in PostgreSQL wiki