I have written a function to read certain columns from a table below using a dynamic query:

CREATE OR REPLACE FUNCTION select_cols ()
    RETURNS SETOF mytable_name
    LANGUAGE plpgsql
    AS $$
DECLARE
    list_of_columns text;
BEGIN
    SELECT
        string_agg(trim(cols::text, '()'), ', ') INTO list_of_columns
    FROM (
        SELECT
            'mytable_name.' || column_name
        FROM
            information_schema.columns
        WHERE
            table_name = 'mytable_name'
            AND column_name LIKE 'rm%_b'
            OR column_name LIKE 'rm%_s') AS cols;
    RETURN query EXECUTE concat(format('select %s from mytable_name', list_of_columns), ' RETURNING *');
END
$$;

Though when I run

select * from select_cols();

it gives me an error : "cannot open EXECUTE query as cursor". I appreciate if someone can help with this issue

CodePudding user response：

You are not returning a set, but you aggreagte the result set for only one table. So, for only one table you can use:

CREATE OR REPLACE FUNCTION select_colsx ()
    RETURNS text
    LANGUAGE plpgsql
    AS $$
DECLARE
    list_of_columns text;
BEGIN
    SELECT
        'select '||string_agg(trim(cols::text, '()'), ', ') || ' from pg_class RETURNING *' 
        INTO list_of_columns
    FROM (
        SELECT
            'pg_class.' || column_name
        FROM
            information_schema.columns
        WHERE
            table_name = 'pg_class'
            AND column_name LIKE 'oid'
            OR column_name LIKE 'relacl') AS cols;
    RETURN  list_of_columns ;
END
$$;

select select_colsx();

DB Fiddle Example

CodePudding user response：

RETURN QUERY EXECUTE was introduced in PostgreSQL 8.4. Upgrade to a less ancient version.