PG and dynamic statements in stored procedures/triggers?

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 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

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?

Thanks:
dd

2011/3/7 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