Re: PG and dynamic statements in stored procedures/triggers? - Mailing list pgsql-general

On Mon, Mar 7, 2011 at 3:16 PM, Bill Thoen <bthoen@gisnet.com> 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