CODEWITHSUNDEEP
arrayspostgresqlparameter-passing
dinesh danny
dinesh danny

Reputation: 103

Bypassing string array as a parameters and using dynamic query in PostgreSQL

I working in PostgreSQL past years, but i had no idea about array concept and how to handle array in PostgreSQL. i need a dynamic query for selecting columns in multiple table and the result will be in cursor, columns names should be dynamically will change.

for e.g (in a multiple table totally 30 columns is there If user need col1, col5,col6,col25), so select statement query will be dynamically will change like: 

select col1, col5,col6,col25 from table ....

another user need col2,col5,col7,col29,col26, select statement will change dynamically as 

select col2,col5,col7,col29,col26 from table .... and so on.

stored procedure passing parameters will be array 

create or replace function func_my_method(check_in character varying, sel character varying[])...

this sel[] contains like

          sel[0]:='col1_name'
          sel[1]:='col5_name'
          sel[2]:='col6_name'
          sel[3]:='col25_name'

so first we have to split the array values in separate variable and these variable will be assumed in select statement will be 

'select'||col1, col5,col6,col25||'from......'

Finally want to say briefly I need to pass an array in parameters and have to separate a array values and it will assign to separate variables. these variable will use to select a statement in dynamic manner

Upvotes: 0

Views: 1955

Answers (1)

pozs
pozs

Reputation: 36214

A bare refcursor can contain any number of columns. Although you'll need a special statement to read from it: FETCH ...

CREATE OR REPLACE FUNCTION func_my_method(check_in text, sel text[], ref refcursor)
  RETURNS refcursor
  LANGUAGE plpgsql
AS $func$
BEGIN
  OPEN ref FOR EXECUTE 'SELECT ' || (SELECT string_agg(quote_ident(c), ', ')
                                     FROM   unnest(sel) c) || ' FROM ...';
  RETURN ref;
END;
$func$;

SELECT func_my_method('check_in', ARRAY['col1', 'col2'], 'sample_name');
FETCH ALL IN sample_name;

http://rextester.com/ZCZT84224

Note: You could omit the refcursor parameter & DECLARE one in your function body. This way PostgreSQL will generate a (non-conflicting) name for the refcursor, which will be returned when calling SELECT func_my_method(...). You'll need that name in the FETCH ... statement.

Update: If you want to fully qualify (some) columns (i.e. write table name & column too), you'll need either:

CREATE OR REPLACE FUNCTION func_my_method2(check_in text, sel text[], ref refcursor)
  RETURNS refcursor
  LANGUAGE plpgsql
AS $func$
BEGIN
  OPEN ref FOR EXECUTE 'SELECT ' || (SELECT string_agg((SELECT string_agg(quote_ident(c), '.')
                                                        FROM   unnest(string_to_array(fq, '.')) c), ', ')
                                     FROM   unnest(sel) fq) || ' FROM ...';
  RETURN ref;
END;
$func$;

SELECT func_my_method2('check_in', ARRAY['col1', 'check_in.col2'], 'sample_name2');
FETCH ALL IN sample_name2;

(this will split the sel parameter into "parts" of the fully qualified name on . -- but have a disadvantage: the table & column names cannot contain .)

Or:

CREATE OR REPLACE FUNCTION func_my_method3(check_in text, sel text[][], ref refcursor)
  RETURNS refcursor
  LANGUAGE plpgsql
AS $func$
BEGIN
  OPEN ref FOR EXECUTE 'SELECT ' || (SELECT string_agg((SELECT string_agg(quote_ident(sel[i][j]), '.')
                                                        FROM   generate_subscripts(sel, 2) j), ', ')
                                     FROM   generate_subscripts(sel, 1) i) || ' FROM ...';
  RETURN ref;
END;
$func$;

SELECT func_my_method3('check_in', ARRAY[ARRAY['check_in', 'col1'], ARRAY['check_in', 'col2']], 'sample_name3');
FETCH ALL IN sample_name3;

(but this has an uncomfortable consequence: since arrays need to be rectangular, all column sub-arrays need to be the same exact dimensions; so you'll need to provide table name for all of the columns or to neither of them.)

http://rextester.com/JNI24740

Upvotes: 2

Related Questions