current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I am trying to run a dynamic query like this (just an idea): 

    declare 
    params text[] ;
    begin 
      params := ARRAY['30', 'sometext'];
      return execute QUERY 'select id, label from "content" where id>$1 and label = $2' using params;
    end ;

The thing is, that my params array can have variable length - I can prepare an appropriate select query, but I would like to execute it using "using" clause, with the array containing all parameters marked with $.

Would be something like that possible or should I build the entire select statement with values already put in a statement (however, this is what I would like to avoid).

share|improve this question
    
how many and what are the values will assign for $1(for ID) and what about for label ? I think there is another good method can be used for your case. –  winged panther Sep 12 '14 at 10:47
    
please visit following link:dba.stackexchange.com/questions/38833/… –  deadman Sep 12 '14 at 11:02
    
Even if this was possible (which it isn't), you'd run into trouble the first time you wanted to mix data types. –  Craig Ringer Sep 12 '14 at 13:16
    
@Winged Panther: the problem is that the number of parameters and their values and their types is unknown. Parameters are passed from client application as a collection of objects (collection of variable length), from which I take a list of values, convert into table and ... yeah - this is the question ;-) –  MichaelCold Sep 17 '14 at 11:10

2 Answers 2

up vote 0 down vote accepted

Even if expanding an array into a parameter-list for USING was possible (which it isn't), you'd run into trouble the first time you wanted to mix data types.

Have you looked into format(..) with the %L specifier? It won't solve the mixed-types issues, but other than that it'll work.

regress=> SELECT format('SELECT a FROM b WHERE z = %L and x = %L and y = %L', VARIADIC ARRAY[1, 2, 3]::integer[]);
                        format                         
-------------------------------------------------------
 SELECT a FROM b WHERE z = '1' and x = '2' and y = '3'
(1 row)

While the values are quoted, that's fine, it's legal to single-quote integers in SQL.

share|improve this answer
up vote 1 down vote

An array is itself a possible value for a parameter, so it cannot be also a container for parameter values.

In your example, the pl/pgsql interpreter would take the entire array after USING as the value for the $1 parameter.

Besides, if the language supported automatic array unnesting into $N parameters, it would generally not fit well because values inside an array are constrained to the same type, whereas $N parameters are not.

share|improve this answer
    
I know that parameter types are the problem, so this is why assumed using array of varchars. In case of numeric parameters (and assuming that the parameter value is correct) it should be converted correctly. In case of problems with conversion an exception should be reised (or I can even test it before I send it to the query). My concern were varchar parameters - I wanted to make sure, that the procedure is SQL-injection-proved. I think that a solution given by Craig Ringer is a good direction, since as far as I know format is safe for SQL injection. –  MichaelCold Sep 17 '14 at 11:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.