Skip site navigation (1) Skip section navigation (2)

Using a variable in sql in a function

From: Peter Jackson <mltasmaniac(at)tasjackson(dot)com>
To: pgsql <pgsql-novice(at)postgresql(dot)org>
Subject: Using a variable in sql in a function
Date: 2010-01-05 13:56:30
Message-ID: 4B43450E.7030706@tasjackson.com (view raw or flat)
Thread:
Lists: pgsql-novice
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;


Responses

pgsql-novice by date

Next:From: A. KretschmerDate: 2010-01-05 14:53:50
Subject: Re: Using a variable in sql in a function
Previous:From: Sean DavisDate: 2010-01-05 11:05:08
Subject: Re: Help to dump tables in a database and restore in another database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group