Postgresql Functions - Using VARIADIC arguments of text type

Question

I am trying to write a function to get the list of objects in schema from Redshift. I have created a dblink from RDS PostgreSQL to Redshift. The query is working just fine when invoked individually, but not working when written inside a function with arguments. I want to pass multiple arguments (schema names), hence I used VARIADIC arguments. The function looks like below -

CREATE FUNCTION f_fetch_tables(VARIADIC list text[]) 
RETURNS VOID
AS $$
    DECLARE
        begin_time TIMESTAMP;
        expire_time TIMESTAMP;
    BEGIN   
        /* To fetch the list of all objects from Redshift */
        EXECUTE 'drop table if exists tmp_rs_obj_list;
        create table tmp_rs_obj_list as 
        SELECT * FROM dblink(''rs_link'',$REDSHIFT$ select * from (select schemaname, 
        tablename from pg_tables UNION select schemaname, viewname from pg_views) where schemaname 
        not in (array_to_string($1,'','')) $REDSHIFT$) AS t1 (schema_nm varchar(30), obj_nm varchar(100))' using list;
    END;
  $$
  LANGUAGE plpgsql
;

The function compiles fine and is created successfully, but I am not able to figure out a way to call it -

Used these calls so far, without any luck -

select f_fetch_tables('{public,pg_catalog}')

ERROR: there is no parameter $1 Where: Error occurred on dblink connection named "unnamed": could not execute query.
select * from f_fetch_tables(VARIADIC '{public,pg_catalog}')

ERROR: there is no parameter $1 Where: Error occurred on dblink connection named "unnamed": could not execute query.

Any suggestions would be really helpful.

Thank you, Kamlesh

klin · Accepted Answer · 2017-08-12 13:53:43Z

There are a few issues with your function. I would suggest to use:

the function format() for easy passing the parameter,
dollar quoted ($fmt$) queries inside execute,
<> all(array) instead of not in operator (you do not have to convert an array to string).

The function with the suggested changes:

create or replace function f_fetch_tables(variadic list text[]) 
returns void
as $$
    declare
        begin_time timestamp;
        expire_time timestamp;
    begin   
        /* to fetch the list of all objects from redshift */
        execute format($fmt$
            drop table if exists tmp_rs_obj_list;
            create table tmp_rs_obj_list as 
                select * 
                from dblink(
                    'rs_link', $redshift$ 
                        select * 
                        from (
                            select schemaname, tablename 
                            from pg_tables 
                            union 
                            select schemaname, viewname 
                            from pg_views) s
                        where schemaname <> all(%L)
                    $redshift$) 
                    as t1 (schema_nm varchar(30), obj_nm varchar(100))
            $fmt$, list);
    end;
$$
language plpgsql;

Note also the proper way of passing arguments to a function with variadic parameter:

select f_fetch_tables('pg_catalog', 'information_schema');
select * from tmp_rs_obj_list;

Pavel Stehule · Accepted Answer · 2017-08-12 11:53:51Z

This issue is not related to variadic parameters - same behave you will get if you use normal parameters too. It is related to dynamic SQL - the queries executed by EXECUTE command from PLpgSQL has own parameter's environment. So you cannot to use variables or param references from function's environment.

This code doesn't work:

CREATE OR REPLACE FUNCTION fx(a int)
RETURNS void AS $$
BEGIN
  EXECUTE 'SELECT * FROM foo WHERE foo.a = $1';
END;
$$ LANGUAGE plpgsql;

In this case, there was not passed any parameter to executed query. $1 is not valid. You should to use a USING clause, when you would to pass some parameters to dynamic SQL.

This code should to work:

CREATE OR REPLACE FUNCTION fx(a int)
RETURNS void AS $$
BEGIN
  EXECUTE 'SELECT * FROM foo WHERE foo.a = $1' USING a;
END;
$$ LANGUAGE plpgsql;

But it doesn't to solve your problem too, because you are using USING clause. But you are use USING clause only on level EXECUTE command - not on dblink level - where it is not supported. dblink API has not nothing similar to USING clause of EXECUTE command. So you have to build native SQL string with unpacked (preevaluated) parameters before you will sent it to dblink API.

You are using two levels of dynamic SQL

EXECUTE
- dblink

dblink doesn't support query parametrization - so you should not to use parameter place holders there .. $x.

In this case is better to serialize a input array to string in top plpgsql level and pass this string like dynamic SQL parameter.

DECLARE serialized_params text;
BEGIN
  serialized_params = (SELECT array_agg(quote_literal(quote_ident(v))) FROM unnest(ARRAY['A','b']) g(v));
  EXECUTE ' ....' USING serialized_params;
END

Collectives™ on Stack Overflow

Postgresql Functions - Using VARIADIC arguments of text type

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related