PostgresSQL dynamic execute with argument values in array
9
votes
2
answers
24664
views
I'm wondering if this is possible in Postgres:
Best explained using a contrived example:
create or replace function test_function(filter_param1 varchar default null
, filter_param2 varchar default null)
returns integer as
$$
declare
stmt text;
args varchar[];
wher varchar[];
retid integer;
begin
if filter_param1 is not null then
array_append(args, filter_param1);
array_append(wher, 'parameter_name = $1');
end if;
if filter_param2 is not null then
array_append(args, filter_param2);
array_append(wher, 'parameter_name = $2');
end if;
stmt := 'select id from mytable where ' || array_to_string(wher, ' or ');
execute stmt into retid using args;
return retid;
end;
$$ language plpgsql;
In Python there is
*args
- perhaps PostgreSQL has a similar mechanism?
EDIT for Erwin Brandstetter questions:
- All filter
parameters will be applied to different columns, but should be AND'ed.
- Returning setof
makes much more sense here.
- All parameters can be of the same column type (ie. varchar
).
Asked by Richard
(216 rep)
Nov 21, 2014, 12:10 PM
Last activity: Mar 20, 2024, 06:22 PM
Last activity: Mar 20, 2024, 06:22 PM