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

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.