CODEWITHSUNDEEP
postgresqlplpgsqldynamic-sql
ddd
ddd

Reputation: 5029

How to use argument for table name in dynamic SQL

I am writing a Postgres function to get the number of new records in a table. Here table name is a variable. 

create or replace function dmt_mas_updates(
    tb_name text,
    days integer)
    returns integer as
$$
declare
    ct integer;
begin
    execute 'select count(*) from $1 where etl_create_dtm > now() - $2 * interval ''1 days'' '
    using tb_name, days into ct;
    return ct;

end;
$$ LANGUAGE 'plpgsql'

When I call the function with select * from dmt_mas_updates('dmt_mas_equip_store_dim',2);, I got syntax error at $1.

If I run the query directly select count(*) from dmt_mas_equip_store_dim where etl_create_dtm >= interval '3 days', it works correctly.

Why am I getting this error? What did I do wrong?

Upvotes: 0

Views: 73

Answers (1)

klin
klin

Reputation: 121604

Per the documentation:

Note that parameter symbols can only be used for data values — if you want to use dynamically determined table or column names, you must insert them into the command string textually.

Use the format() function:

create or replace function dmt_mas_updates(
    tb_name text,
    days integer)
    returns integer as
$$
declare
    ct integer;
begin
    execute format(
        'select count(*) from %I where etl_create_dtm > now() - $1 * interval ''1 days'' ',
        tb_name)
    using days into ct;
    return ct;

end;
$$ LANGUAGE 'plpgsql';

Upvotes: 2

Related Questions