Обсуждение: Using a variable in sql in a function

Hiya list,

I was wondering if this is possible or not. If yes how?

I want to use a changing where condition on the query, subject to the
value I pass in to the function. I have tried the below but it fails in
use. This is with PostgreSql 8.3.1.
The query will end up a bit more complicated than below.  With a set
where clause it returns the rows/results expected.

CREATE OR REPLACE FUNCTION test(period_type text) RETURNS SETOF test_type AS
$BODY$
DECLARE
o record;
r test_type;

where_text character varying;

BEGIN

IF period_type = 'current' THEN
  where_text := 'WHERE field1 IS NULL';
elseif period_type = 'old' THEN
  where_text := 'WHERE field1 IS NOT NULL';
elseif ....
  (more conditions here with various where clauses)
else
  RETURN;
end if;

FOR o IN
  SELECT distinct(col_id) as id
   FROM table1
   JOIN table2
   ON col_id = t2_t1_id
   where_text  <-- this bit
  LOOP

  FOR r IN
    SELECT a.col1,a.col2,b.col3,b.col4
   FROM table2 as a
   JOIN table3 as b
   ON t2_t1_id = t3_t1_id
   WHERE t2_t1_id = o.id

LOOP
RETURN NEXT r;
END LOOP;
END LOOP;

RETURN;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;

In response to Peter Jackson :
> Hiya list,
>
> I was wondering if this is possible or not. If yes how?
>
> I want to use a changing where condition on the query, subject to the
> value I pass in to the function. I have tried the below but it fails in
> use. This is with PostgreSql 8.3.1.

You can do that, but you need to build a complete string containing your
full query and execute this string.

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

you could possibly do it like this.

FOR o in SELECT distinct(col_id) as id
    FROM table1
    JOIN table2
    ON col_id = t2_t1_id
   WHERE
     CASE  period_type
       WHEN 'CURRENT' THEN field1 IS NULL
       WHEN 'old'     THEN field1 IS NOT NULL

...etc...

     END
   LOOP