Re: SQL to list databases?

От:

Steve Crawford <scrawford@pinpointresearch.com>

Дата:

23 января 2003 г. в 19:57:25

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

Re: SQL to list databases?

От:

Keith Keller <kkeller-postgres@wombat.san-francisco.ca.us>

Дата:

23 января 2003 г. в 19:55:13

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

Re: SQL to list databases?

От:

Roberto Mello <rmello@cc.usu.edu>

Дата:

24 января 2003 г. в 00:27:34

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.

Re: SQL to list databases?

От:

Horst Herb <hherb@gnumed.net>

Дата:

29 января 2003 г. в 01:06:46

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

Re: calling function from rule

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

23 января 2003 г. в 10:42:27

"Tambet Matiisen"  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/rules-status.html
		regards, tom lane

Re: calling function from rule

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

23 января 2003 г. в 12:16:25

"Tambet Matiisen"  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

SQL to list databases?

От:

Ben Siders <bsiders@cms-stl.com>

Дата:

23 января 2003 г. в 15:26:37


Is there a query that will return all the databases available, similar 
to what psql -l does?

Re: SQL to list databases?

От:

Kristopher Yates <kris@grinz.com>

Дата:

23 января 2003 г. в 18:55:09

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

Re: SQL to list databases?

От:

Guy Fraser <guy@incentre.net>

Дата:

24 января 2003 г. в 14:10:23

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 u ON 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_namespace n 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 
>  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
>>
> 
> 
>

Re: SQL to list databases?

От:

Larry Rosenman <ler@lerctr.org>

Дата:

23 января 2003 г. в 18:32:33



--On Thursday, January 23, 2003 12:56:50 -0600 Ben Siders 
 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

calling function from rule

От:

"Tambet Matiisen" <t.matiisen@aprote.ee>

Дата:

23 января 2003 г. в 05:57:14


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:

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 the view. 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

Re: calling function from rule

От:

"Tambet Matiisen" <t.matiisen@aprote.ee>

Дата:

23 января 2003 г. в 11:19:40

> 
> "Tambet Matiisen"  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

Re: calling function from rule

От:

"Tambet Matiisen" <t.matiisen@aprote.ee>

Дата:

24 января 2003 г. в 04:07:56



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