Thread: calling function from rule
I have a view and update rule on it, which updates another table. Now I would like to call a function, after update of thetable is done. Also the function must be called in the same transaction as update. I tried to define an additional updaterule on the view, but doing a SELECT in update rule spoils the affected records count: hekotek=# create table a(id integer); CREATE TABLE hekotek=# create view v as select * from a; CREATE VIEW hekotek=# create rule r as on update to v do instead update a set id = new.id where id = old.id; CREATE RULE hekotek=# create rule r1 as on update to v do select 1; CREATE RULE hekotek=# insert into a values (1); INSERT 1194985 1 hekotek=# update v set id = 2;?column? ---------- 1 (1 row) hekotek=# drop rule r1 on v; DROP RULE hekotek=# update v set id = 3; UPDATE 1 It's important to me to have correct affected records count returned. I cannot use triggers, because views can't have triggers.I also cannot define the trigger on the table, because the function must be called only when updated through theview. I think I could define the rule to call the function in update query for some dummy table: update dummy set field= function(parameters). But is there a better way? Tambet
"Tambet Matiisen" <t.matiisen@aprote.ee> writes: > I have a view and update rule on it, which updates another table. Now I would like to call a function, after update ofthe table is done. Also the function must be called in the same transaction as update. I tried to define an additionalupdate rule on the view, but doing a SELECT in update rule spoils the affected records count: Try 7.3, we changed the rules about returned records count. http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/rules-status.html regards, tom lane
> > "Tambet Matiisen" <t.matiisen@aprote.ee> writes: > > I have a view and update rule on it, which updates another > table. Now I would like to call a function, after update of > the table is done. Also the function must be called in the > same transaction as update. I tried to define an additional > update rule on the view, but doing a SELECT in update rule > spoils the affected records count: > > Try 7.3, we changed the rules about returned records count. > http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/ru > les-status.html > I have 7.3. When rule and action are the same, everything works fine. Doing an insert in update rule and opposite are OK too. Problem is, when I do select in insert/update/delete rule. Then the result of select is returned instead of command status, even if the select is done in non-instead rule and there is unconditional instead rule. See the example in my previous mail. Tambet
"Tambet Matiisen" <t.matiisen@aprote.ee> writes:
>> Try 7.3, we changed the rules about returned records count.
> I have 7.3. When rule and action are the same, everything works fine.
> Doing an insert in update rule and opposite are OK too. Problem is,
> when I do select in insert/update/delete rule. Then the result of
> select is returned instead of command status, even if the select
> is done in non-instead rule and there is unconditional instead rule.
Oh, I think your complaint is really about the fact that psql doesn't
print the command status if it got any tuples (or even just a tuple
descriptor) in the result series. AFAICT the information returned by
the backend is sensible in this situation: the "UPDATE 1" status message
*is* returned and is available from PQcmdStatus. psql is just choosing
not to print it. I'm not sure that that's wrong, though.
regards, tom lane
Is there a query that will return all the databases available, similar to what psql -l does?
--On Thursday, January 23, 2003 12:56:50 -0600 Ben Siders <bsiders@cms-stl.com> wrote: > > Is there a query that will return all the databases available, similar to > what psql -l does? > $ psql -E -l ********* QUERY ********** SELECT d.datname as "Name", u.usename as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding" FROM pg_catalog.pg_database d LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid ORDER BY 1; ************************** > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
I have been wondering the same thing.. Thanks Kris Ben Siders wrote: > > Is there a query that will return all the databases available, similar > to what psql -l does? > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > . >
On Thu, Jan 23, 2003 at 12:56:50PM -0600, Ben Siders wrote: > > Is there a query that will return all the databases available, similar > to what psql -l does? Any time you wish to know what psql is doing with ''meta'' commands (like the \ commands), use the -E switch, which will display the queries it's sending to the backend. man psql for more details. --keith -- kkeller@speakeasy.net public key: http://wombat.san-francisco.ca.us/kkeller/kkeller.asc alt.os.linux.slackware FAQ: http://wombat.san-francisco.ca.us/cgi-bin/fom
psql -E causes psql to show it's "behind the scenes" queries to try: psql -lE (that's a lower case ell before the E) Cheers, Steve On Thursday 23 January 2003 10:56 am, Ben Siders wrote: > Is there a query that will return all the databases available, similar > to what psql -l does? > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
On Thu, Jan 23, 2003 at 02:33:55PM -0600, Kristopher Yates wrote:
> I have been wondering the same thing..
SELECT * FROM pg_databases ?
> >Is there a query that will return all the databases available, similar
> >to what psql -l does?
The handy "-E" flag to psql helps:
roberto@brasileiro:~$ psql -E -l
********* QUERY **********
SELECT d.datname as "Name", u.usename as "Owner", pg_encoding_to_char(d.encoding) as "Encoding"
FROM pg_database d LEFT JOIN pg_user u ON d.datdba = u.usesysid
ORDER BY 1;
**************************
List of databases Name | Owner | Encoding
----------------+------------+----------addresses | windozefoo | LATIN1foobar | roberto |
LATIN1openacs-4 | roberto | LATIN1template0 | postgres | LATIN1template1 | postgres | LATIN1
-Roberto
--
+----| Roberto Mello - http://www.brasileiro.net/ |------+
+ Computer Science Graduate Student, Utah State University +
+ USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ +
Dente lupus, cornu taurus petit.
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Thursday, January 23, 2003 7:01 PM > To: Tambet Matiisen > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] calling function from rule > > > "Tambet Matiisen" <t.matiisen@aprote.ee> writes: > >> Try 7.3, we changed the rules about returned records count. > > > I have 7.3. When rule and action are the same, everything > works fine. > > Doing an insert in update rule and opposite are OK too. Problem is, > > when I do select in insert/update/delete rule. Then the result of > > select is returned instead of command status, even if the select > > is done in non-instead rule and there is unconditional instead rule. > > Oh, I think your complaint is really about the fact that psql doesn't > print the command status if it got any tuples (or even just a tuple > descriptor) in the result series. AFAICT the information returned by > the backend is sensible in this situation: the "UPDATE 1" > status message > *is* returned and is available from PQcmdStatus. psql is > just choosing > not to print it. I'm not sure that that's wrong, though. > Thanks, I only tested it with psql and got worried. Tambet
Hi
To make it easier to do this in SQL you can create a view like this :
CREATE VIEW db_list AS SELECT d.datname as "Name", u.usename as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding)as "Encoding" FROM pg_catalog.pg_database d LEFT JOIN pg_catalog.pg_user
uON d.datdba = u.usesysid ORDER BY 1;
Note: the "select" statement comes from the post I am replying from.
Then all you have to do is :
select * from db_list;
For example this is my output :
foobar=# select * from db_list; Name | Owner | Encoding
-----------+-------+----------- foobar | turk | SQL_ASCII template0 | pgsql | SQL_ASCII template1 | pgsql |
SQL_ASCII
(3 rows)
Or :
foobar=# select "Name","Owner" from db_list where "Owner" != 'pgsql'; Name | Owner
--------+------- foobar | turk
(1 row)
Using psql -E {database} interactivly
Or
psql -E -c "\{command}" {database}
Example:
user@host:~$ psql -E -c "\dt" template1
********* QUERY **********
SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type", u.usename as "Owner"
FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner LEFT JOIN
pg_catalog.pg_namespacen ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
You can collect the SQL for other helpful commands and build views like above,
then you can query the view for more specific information.
I hope this is helpful.
Guy
PS: If you create these "views" in template1 before you create your other
databases, these views will be included in new databases automaticaly.
Larry Rosenman wrote:
>
>
> --On Thursday, January 23, 2003 12:56:50 -0600 Ben Siders
> <bsiders@cms-stl.com> wrote:
>
>>
>> Is there a query that will return all the databases available, similar to
>> what psql -l does?
>>
> $ psql -E -l
> ********* QUERY **********
> SELECT d.datname as "Name",
> u.usename as "Owner",
> pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
> FROM pg_catalog.pg_database d
> LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid
> ORDER BY 1;
> **************************
>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>
>
>
On Fri, 24 Jan 2003 05:56, you wrote: > Is there a query that will return all the databases available, similar > to what psql -l does? select * from pg_database; Horst