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

Queries on the fly

From: Greg Lindstrom <greg(dot)lindstrom(at)novasyshealth(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Queries on the fly
Date: 2006-11-14 20:04:21
Message-ID: 455A2145.1030004@novasyshealth.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hello-

I am running PostgreSQL 8.0 on a Gento system and would like to execute 
SQL that I have stored in another table and capture the results of the 
query.  I have a set of conditions which must be true for an event to 
occur, so I have a table named "predicates" which holds "N" tests, all 
with effective and end dates defining when this particular test should 
be applied to my system.  I have a user defined function where I would 
like to pull all of the tests from the predicates table, run the 
condition (stored in a column named 'SQL' in the table) and compare it 
to a desired result.

An example of a test would be "SELECT INTO ret_value gender='M' as 
gender FROM members WHERE member_nbr = member_nbr_in"

My stripped down function looks something like

DECLARE
   ret_value Boolean := False;
   query VarChar(128);
BEGIN
   SELECT INTO query sql FROM predicates WHERE id = 1;

    EXECUTE query;
    -- I would like the result of the query to be here so I can return it...

    RETURN ret_value;
END;

Is this type of thing possible?  It would allow us to add tests and 
actions "on the fly" without having to modify our code.  Any help you 
can supply would be greatly appreciated.

--greg

-- 
Greg Lindstrom               501 975.4859 (office)
Programmer                   501 219-4455 (fax)
NovaSys Health               greg(dot)lindstrom(at)novasyshealth(dot)com
Little Rock, Arkansas

"We are the music makers, and we are the dreamers of dreams."  W.W.



pgsql-novice by date

Next:From: Tom AllisonDate: 2006-11-15 00:51:24
Subject: Re: hardware - generic
Previous:From: Richard Broersma JrDate: 2006-11-14 17:11:01
Subject: Re: Compiling Stored Procedure

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