Обсуждение: PG and dynamic statements in stored procedures/triggers?

On Mar 7, 2011, at 8:02 PM, Durumdara wrote:

> Hi!
>
> In other RDBMS I found a way to make dynamic statements.
> I can use variables, or concat the SQL segments, and execute it all.
>
> :tablename = call CreateTempTable;
> insert into :tablename ....
> drop table :tablename
>
> or (FireBird like cursor handling):
>
> sql = "select * from " || :tablename || " where..."
> for select :sql ....
> ...
>
> Can I do same thing in PGSQL too?
>
> Thanks:
>    dd

You can use EXECUTE dynamic Command of plgpsql:
http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com

On Monday, March 07, 2011 6:32:44 am Durumdara wrote:
> Hi!
>
> In other RDBMS I found a way to make dynamic statements.
> I can use variables, or concat the SQL segments, and execute it all.
>
> :tablename = call CreateTempTable;
>
> insert into :tablename ....
> drop table :tablename
>
> or (FireBird like cursor handling):
>
> sql = "select * from " || :tablename || " where..."
> for select :sql ....
> ...
>
> Can I do same thing in PGSQL too?
>
> Thanks:
>    dd

http://www.postgresql.org/docs/9.0/interactive/plpgsql-statements.html#PLPGSQL-
STATEMENTS-EXECUTING-DYN
--
Adrian Klaver
adrian.klaver@gmail.com

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.

>
> Thanks:
>     dd

--
Adrian Klaver
adrian.klaver@gmail.com

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.

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@gmail.com

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