Re: PG and dynamic statements in stored procedures/triggers?

On Monday, March 07, 2011 1:16:11 pm Bill Thoen wrote:

>
> For example, I have a need for a tool that gets an initial record id
> from the user, then it looks up that key and finds the primary keys of
> two other tables related to the firstkey, then it looks those tables up
> and displays the data from each side by side so I can check the
> differences between the records. (Basically, it's a case of data from
> two vendors that carry a common key, and I'm just spot checking). I've
> been using interactive psql, but I thought an app as simple as this is
> in concept wouldn't be so hard to do, but it is if you don't know enough
> of what's in the API like, isn't there a function to enumerate a table's
> attributes?. Or how do you capture the results of a select that calls a
> function in SQL? (e.g.:
> \set myResults
>
> :myResults = SELECT myFunction();
>
> -- this won't fly; nor will this:
> SELECT INTO :myResults myFunction();

A possible solution from here:
http://www.postgresql.org/docs/9.0/interactive/sql-createtableas.html

"
PREPARE recentfilms(date) AS
SELECT * FROM films WHERE date_prod > $1;
CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
EXECUTE recentfilms('2002-01-01');
"

>
> Anyway, I'm begining to see that I had some misconceptions about what
> you can do within SQL and what you're better off doing in plpgsql. Or C.
> Read the whole section on variables in the manual. That's very good
> advice. In fact, peruse it. Because if you read it lightly, you'll have
> to to go over it again and again.
>
> But after reading your note, dynamic SQL seems like it might be just
> what I'm looking for too. Didn't realize it was an option, since I see
> it's documented near the end of the manual, and there's only so much
> RTFMing I can do at a sitting, so that's all new territory to me. But if
> it works like you've sketched out here... well I'm going to try it and see.

On Postgres 9.0+ there is also DO
http://www.postgresql.org/docs/9.0/interactive/sql-do.html
--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com