On Mon, Mar 7, 2011 at 3:16 PM, Bill Thoen <bthoen@xxxxxxxxxx> wrote: > On 3/7/2011 7:55 AM, Adrian Klaver wrote: >> >> On Monday, March 07, 2011 6:45:11 am Durumdara wrote: >>> >>> Hi! >>> >>> Thanks! >>> >>> How do I create "cursor" or "for select" in PGSQL with dynamic way? >>> >>> For example >>> >>> :tbl = GenTempTableName() >>> >>> insert into :tbl... >>> insert into :tbl... >>> insert into :tbl... >>> >>> for select :part_id from :tbl begin >>> exec 'select count(*) from subitems where id = ?' using :part_id into >>> >>> :sumof >>> >>> update :tbl set sumof = :sumof where part_id=:part_id >>> end; >>> >>> Can you show me same example? >> >> There are examples in the docs at the link provided. Though I would >> suggest >> reading the pl/pgsql documentation from the beginning to get an idea of >> its >> structure. > > You won't find this easy. I've spent an awful lot of time the last two days > trying to figure out how to pass variables between SQL and plpgsql, and the > examples don't cover all the things you'd think you should be able to do but > because Postgres SQL doesn't have variables. What it does have comes from > psql and they seem to be more like text replacement placeholders than > variables you can evaluate. > > 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(); > > 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. correct. psql variables are completely client side and IMO, perhaps controversially, useless. for non-trivial processing you should dip into the server for pl/pgsql, perhaps the finest data processing language ever invented, or the application side if you need to manage transaction state. recent postgres supports 'DO' commands, allowing to access pl/pgsql power without creating the function first. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
