CODEWITHSUNDEEP
postgresqlpostgresql-9.5
Maharaj
Maharaj

Reputation: 87

Executing query inside variable in PostgreSQL function

I'm new to postgres, for a scenario i stored SQL statements inside a table with respective to table name. in the function i'm trying to filter the table name by passing them as a parameter to get the query from the table. But when i execute the query from the variable it gives out error

"SQL Error [42P01]: ERROR: relation "public.table_name" does not exist Where: PL/pgSQL function ops_data_refresh(text) line 45 at EXECUTE"

execute format('select query from public.ops_dw_table_load where target_table=''%s'' and is_active =true',main_table)
into qry1;

if qry1 is not null then

raise notice '%',qry1;
execute qry1;

raise notice output insert into public.table_name select * from stage.table_name;

with raise notice im able to see the query which is in the table, if I run it manually things are working fine. but when running from function it throws the above error.

Upvotes: 1

Views: 2370

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246288

There is an SQL injection bug in your code. It should be:

EXECUTE format('SELECT ... target_table = %L ...', main_table);

But the problem is in the second EXECUTE: the query references a table that does not exist. Either change the query or create the table.

Upvotes: 0

Related Questions