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.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
