Sample Header Ad - 728x90

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