Re: [SQL] float and numeric in 7.3
Tomasz Myrta <[EMAIL PROTECTED]> writes: > 7.3 can't automaticaly cast float into numeric? Nope, we tightened it up --- that's not an implicit cast anymore. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] update and IN vs. EXISTS
> -Original Message- > From: pginfo [mailto:[EMAIL PROTECTED]] > Sent: Saturday, February 01, 2003 3:50 PM > To: Bruno Wolff III > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] update and IN vs. EXISTS > > > > > Bruno Wolff III wrote: > > > On Sat, Feb 01, 2003 at 12:40:00 +0100, > > pginfo <[EMAIL PROTECTED]> wrote: > > > > > > If I try to execute: > > >update Table1 set fieldForUpdate = 1 where ID IN > (select T2.ID from > > > Table2); > > > it is running very slow. > > > > You might try: > > update Table1 set fieldForUpdate = 1 from Table2 where > Table1.id = Table2.id; > > > > It is great.It takes 122 sec. > With IN it takes 8000 sec. > > > This uses a nonstandard postgres extension and may not be > portable, if that > > is a concern. > > > > How to resolve the problem with the standart?regards, > iavn. > This should work as well: update Table1 set fieldForUpdate = 1 where EXISTS (select 1 from Table2 where Table1.IDS=Table2.IDS ); Tambet ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Commenting PLPGSQL
Is it possible to have comment lines inside PLPGSQL?? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] TEMP tables
Lex Berezhny wrote: hi, I have a plpgsql procedure that needs to create a temporary table, use it as a stack internally, and then disgard it when the procedure exits. What are the recommendations or solutions on using temporary tables inside functions on a per call basis? thanks a lot, Instead of Bruce solution you can: 1. Create your temporary table only once after you login into database. Before each execution of your function just delete/truncate this temporary table. You don't need to drop this table, because it is automatically dropped when the session is finished. 2. Use global table as a stack. Use some sessionid to indetify stack for each procedure execution. Create index on sessionid. There is one more advantage on this solution - you can use views without recreating them. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Commenting PLPGSQL
David Durst wrote: Is it possible to have comment lines inside PLPGSQL?? -- comment Regards, Tomasz Myrta ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Format in psql
Hi, I am using pgsql-7.2.3. Can i able to format the output of a SELECT sql in psql as perl format? Something like, +--++ | work_desc_id | short_desc | +--++ |1 | Short description line 1 | | | Short description line 2 | | | Short description line 3 | | | Short description line n | +--++ If yes, how can i do this? I have gone through the manual page of psql but such formatting information donot appear over there. So, hope to get some details over here. A link would be enough. regards, bhuvaneswaran ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Format in psql
Bhuvan A wrote: Hi, I am using pgsql-7.2.3. Can i able to format the output of a SELECT sql in psql as perl format? Something like, +--++ | work_desc_id | short_desc | +--++ |1 | Short description line 1 | | | Short description line 2 | | | Short description line 3 | | | Short description line n | +--++ If yes, how can i do this? I have gone through the manual page of psql but such formatting information donot appear over there. So, hope to get some details over here. A link would be enough. man psql. Inside psql: \pset border 2 Regards, Tomasz Myrta ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Format in psql
On Monday 03 February 2003 11:54, Bhuvan A wrote: > Hi, > > I am using pgsql-7.2.3. Can i able to format the output of a SELECT sql in > psql as perl format? > > Something like, > +--++ > | work_desc_id | short_desc | > +--++ > |1 | Short description line 1 | > | > | | Short description line 2 | > | | Short description line 3 | > | | Short description line n | > +--++ 'fraid I don't understand what you mean by "perl" format. You can get format in the style above by executing \pset border 2 in psql. (SQL*Plus fans should issue \pset border 0 and recompile psql without readline support to simulate an Oracle environment ;-) Place this setting in your .psqlrc file to make it permanent. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] cannot EXPLAIN query...
Hi, the query is running fine but i cannot EXPLAIN or (ANALYZE) it. I am seeing this message for the first time: tradein_clients=# SELECT count(*) from shippers1 where city='DELHI'; +---+ | count | +---+ | 2 | +---+ (1 row) tradein_clients=# tradein_clients=# explain SELECT count(*) from shippers1 where city='DELHI'; ERROR: get_names_for_var: bogus varno 5 tradein_clients=# i can paste the nasty view definations if nothing is obvious till now. regds MAllah. Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)26152172 (221) (L) 9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] cannot EXPLAIN query...
Rajesh Kumar Mallah. wrote: Hi, the query is running fine but i cannot EXPLAIN or (ANALYZE) it. I am seeing this message for the first time: tradein_clients=# SELECT count(*) from shippers1 where city='DELHI'; +---+ | count | +---+ | 2 | +---+ (1 row) tradein_clients=# tradein_clients=# explain SELECT count(*) from shippers1 where city='DELHI'; ERROR: get_names_for_var: bogus varno 5 tradein_clients=# i can paste the nasty view definations if nothing is obvious till now. regds MAllah. Isn't it a very long view? I found some limitations of explain, but possibly they had something to cygwin enviroment. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] cannot EXPLAIN query...
"Rajesh Kumar Mallah." <[EMAIL PROTECTED]> writes: > tradein_clients=# explain SELECT count(*) from shippers1 where city='DELHI'; > ERROR: get_names_for_var: bogus varno 5 What version is this? ISTR having fixed some bugs that might cause that. > i can paste the nasty view definations if nothing is obvious till > now. If it's a current release, we need to see *all* the schema definitions referenced by the query --- views and tables. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] cannot EXPLAIN query...
It is PostgreSQL 7.3.0 on Linux.
Sorry Postgresql has really made my VIEWS ugly.
It wasnt' so when i fed them.
I wish pgsql stores the create view defination some day ,
just like it does for indexes (pg_get_indexdef)
Here is the EXPLAIN ANALYZE output of a query that is working
on the view.
i find that whenever i put any filter expression on the select
for any feild it stops working.
tradein_clients=# explain analyze SELECT * from shippers1 ;
QUERY PLAN
---
Hash Join (cost=31.67..26780.73 rows=2 width=339) (actual time=45.18..6072.38
rows=732 loops=1)
Hash Cond: ("outer".id = "inner".id)
Join Filter: ("inner".source = "outer".source)
-> Subquery Scan b (cost=0.00..26737.99 rows=492 width=307) (actual
time=0.14..6015.04 rows=2293 loops=1)
-> Append (cost=0.00..26737.99 rows=492 width=307) (actual
time=0.13..6001.13 rows=2293 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..6739.42 rows=249 width=307)
(actual time=0.12..2982.18 rows=321 loops=1)
-> Index Scan using eyp_listing_category_id,
eyp_listing_category_id, eyp_listing_category_id, eyp_listing_category_id,
eyp_listing_category_id on eyp_listing (cost=0.00..6739.42 rows=249 width=307)
(actual time=0.11..2979.18 rows=321 loops=1)
Index Cond: ((category_id = 1142) OR (category_id = 1143)
OR (category_id = 1145) OR (category_id = 1259) OR (category_id = 1161))
Filter: ((amount > 0) AND (status = 'a'::character varying)
AND (email IS NOT NULL))
-> Subquery Scan "*SELECT* 2" (cost=0.00..9288.33 rows=77 width=286)
(actual time=0.65..162.03 rows=112 loops=1)
-> Seq Scan on iid_listing (cost=0.00..9288.33 rows=77
width=286) (actual time=0.63..161.02 rows=112 loops=1)
Filter: (((category_id = 1142) OR (category_id = 1143) OR
(category_id = 1145) OR (category_id = 1259) OR (category_id = 1161)) AND (amount > 0)
AND (status = 'a'::character varying) AND (email IS NOT NULL))
-> Subquery Scan "*SELECT* 3" (cost=10701.96..10710.24 rows=166
width=248) (actual time=2824.89..2851.45 rows=1860 loops=1)
-> Unique (cost=10701.96..10710.24 rows=166 width=248) (actual
time=2824.86..2837.43 rows=1860 loops=1)
-> Sort (cost=10701.96..10706.10 rows=1656 width=248)
(actual time=2824.85..2831.21 rows=2790 loops=1)
Sort Key: branding_master.company_id
-> Hash Join (cost=339.35..10613.44 rows=1656
width=248) (actual time=33.62..2798.98 rows=2790 loops=1)
Hash Cond: ("outer".company_id =
"inner".company_id)
-> Seq Scan on branding_master
(cost=0.00..7171.80 rows=36254 width=242) (actual time=0.07..2620.57 rows=38766
loops=1)
Filter: ((old_company_id = 0) AND (status
= 'a'::character varying) AND (email IS NOT NULL))
-> Hash (cost=331.00..331.00 rows=3339
width=6) (actual time=32.92..32.92 rows=0 loops=1)
-> Seq Scan on branding_sp_category
(cost=0.00..331.00 rows=3339 width=6) (actual time=0.06..26.18 rows=3892 loops=1)
Filter: ((category_id = 1142) OR
(category_id = 1143) OR (category_id = 1145) OR (category_id = 1259) OR (category_id =
1161))
-> Hash (cost=29.74..29.74 rows=774 width=15) (actual time=43.78..43.78 rows=0
loops=1)
-> Seq Scan on approved_profiles a (cost=0.00..29.74 rows=774 width=15)
(actual time=40.64..42.36 rows=778 loops=1)
Total runtime: 6074.86 msec
(26 rows)
Time: 7080.76 ms
tradein_clients=#
And Following are the VIEWS & Tables:
tradein_clients=# \d shippers1
View "shipping_corner.shippers1"
+--+---+---+
| Column | Type| Modifiers |
+--+---+---+
| co_name | character varying | |
| address | character varying | |
| city | character varying | |
| pin_code | character varying | |
| phone| character varying | |
| fax | character varying | |
| contact | character varying | |
| email| character varying | |
| size | character varying | |
| paid | boolean | |
+--+---+---+
View definition:
Re: [SQL] cannot EXPLAIN query...
"Rajesh Kumar Mallah." <[EMAIL PROTECTED]> writes: > It is PostgreSQL 7.3.0 on Linux. Try 7.3.1 then. I think this is this problem: 2002-12-06 14:28 tgl * src/backend/commands/explain.c (REL7_3_STABLE): Explain's code for showing quals of SubqueryScan nodes has been broken all along; not noticed till now. It's a scan not an upper qual ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] cannot EXPLAIN query...
Thank you . But i have a problem , I think if i do that i will hve to immediately upgrade all the 7.3.0 clients in other machines to 7.3.1 rite? regds Mallah. On Monday 03 February 2003 09:10 pm, Tom Lane wrote: > "Rajesh Kumar Mallah." <[EMAIL PROTECTED]> writes: > > It is PostgreSQL 7.3.0 on Linux. > > Try 7.3.1 then. I think this is this problem: > > 2002-12-06 14:28 tgl > > * src/backend/commands/explain.c (REL7_3_STABLE): Explain's code > for showing quals of SubqueryScan nodes has been broken all along; > not noticed till now. It's a scan not an upper qual ... > > regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] cannot EXPLAIN query...
"Rajesh Kumar Mallah." <[EMAIL PROTECTED]> writes: > I think if i do that i will hve to immediately upgrade > all the 7.3.0 clients in other machines to 7.3.1 rite? No. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] cannot EXPLAIN query...
No, you can mix them. --- Rajesh Kumar Mallah. wrote: > > > Thank you . But i have a problem , > > I think if i do that i will hve to immediately upgrade > all the 7.3.0 clients in other machines to 7.3.1 rite? > > > regds > Mallah. > > On Monday 03 February 2003 09:10 pm, Tom Lane wrote: > > "Rajesh Kumar Mallah." <[EMAIL PROTECTED]> writes: > > > It is PostgreSQL 7.3.0 on Linux. > > > > Try 7.3.1 then. I think this is this problem: > > > > 2002-12-06 14:28 tgl > > > > * src/backend/commands/explain.c (REL7_3_STABLE): Explain's code > > for showing quals of SubqueryScan nodes has been broken all along; > > not noticed till now. It's a scan not an upper qual ... > > > > regards, tom lane > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] cannot EXPLAIN query...
> > Sorry Postgresql has really made my VIEWS ugly. > It wasnt' so when i fed them. > > I wish pgsql stores the create view defination some day , > just like it does for indexes (pg_get_indexdef) > Did you ever try SELECT * FROM pg_views ; It definitely has all view definitions. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] cannot EXPLAIN query...
Thanks , if that is so i am upgrading it right away and posting you the results. Its my live DB server :-) Regds mallah. On Monday 03 February 2003 09:15 pm, Tom Lane wrote: > "Rajesh Kumar Mallah." <[EMAIL PROTECTED]> writes: > > I think if i do that i will hve to immediately upgrade > > all the 7.3.0 clients in other machines to 7.3.1 rite? > > No. > > regards, tom lane -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)26152172 (221) (L) 9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] cannot EXPLAIN query...
On Monday 03 February 2003 09:20 pm, Christoph Haller wrote: > > Sorry Postgresql has really made my VIEWS ugly. > > It wasnt' so when i fed them. > > > > I wish pgsql stores the create view defination some day , > > just like it does for indexes (pg_get_indexdef) > > Did you ever try > > SELECT * FROM pg_views ; i thing when you do a \d it uses that only. in any case i have verified that the content in them are equally messed up. regds mallah. > > It definitely has all view definitions. > > Regards, Christoph -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)26152172 (221) (L) 9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] cannot EXPLAIN query...
Hmmm... upgrade to 7.3.1 was not that smooth.. after upgrade i could not run a single query.. tradein_clients=> SELECT * from hogs; ERROR: current transaction is aborted, queries ignored until end of transaction block tradein_clients=> any other query seems to be giving the same ERROR. check the message below on psql start (7.3.1) with a 7.3.1 server. PS: i applied the heir patch though ... ;-) will try again without that. [postgres@ns3 postgres]$ psql -Upostgres -h66.234.10.12 tradein_clients >> ERROR: nodeRead: did not find '}' at end of plan node Welcome to psql 7.3.1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit tradein_clients=> regds mallah. On Monday 03 February 2003 09:15 pm, Tom Lane wrote: > "Rajesh Kumar Mallah." <[EMAIL PROTECTED]> writes: > > I think if i do that i will hve to immediately upgrade > > all the 7.3.0 clients in other machines to 7.3.1 rite? > > No. > > regards, tom lane -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)26152172 (221) (L) 9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] cannot EXPLAIN query...
Hi ,
This is to confirm that the EXPLAIN problem
does not occur anymore after successfully upgrading
to 7.3.1 from 7.3.0
Thanks everyone.
Regards
Mallah.
explain select * from shippers1 where city='DELHI';
QUERY
PLAN---
Nested Loop (cost=0.00..26461.22 rows=1 width=339)
-> Subquery Scan b (cost=0.00..26448.53 rows=477 width=307)
Filter: (city = 'DELHI'::character varying)
-> Append (cost=0.00..26448.53 rows=477 width=307)
-> Subquery Scan "*SELECT* 1" (cost=0.00..6739.42 rows=249 width=307)
-> Index Scan using eyp_listing_category_id,
eyp_listing_category_id,
eyp_listing_category_id, eyp_listing_category_id,
eyp_listing_category_id on
eyp_listing (cost=0.00..6739.42 rows=249 width=307)
Index Cond: ((category_id = 1142) OR (category_id = 1143) OR
(category_id = 1145) OR (category_id = 1259) OR
(category_id = 1161)) Filter: ((amount > 0) AND (status =
'a'::character varying) AND (email
IS NOT NULL)) -> Subquery Scan "*SELECT* 2"
(cost=0.00..9288.33 rows=77 width=286)
-> Seq Scan on iid_listing (cost=0.00..9288.33 rows=77
width=286)
Filter: (((category_id = 1142) OR (category_id = 1143) OR
(category_id
= 1145) OR (category_id = 1259) OR (category_id = 1161))
AND (amount >
0) AND (status = 'a'::character varying) AND (email IS NOT
NULL)) -> Subquery Scan "*SELECT* 3" (cost=10413.26..10420.79 rows=151
width=248)
-> Unique (cost=10413.26..10420.79 rows=151 width=248)
-> Sort (cost=10413.26..10417.02 rows=1506 width=248)
Sort Key: branding_master.company_id
-> Hash Join (cost=339.35..10333.74 rows=1506
width=248)
Hash Cond: ("outer".company_id =
"inner".company_id)
-> Seq Scan on branding_master
(cost=0.00..7171.80
rows=32985 width=242)
Filter: ((old_company_id = 0) AND (status =
'a'::character varying) AND (email IS NOT
NULL) AND
(eyp_paid IS NULL) AND (iid_paid IS
NULL)) -> Hash (cost=331.00..331.00 rows=3339
width=6)
-> Seq Scan on branding_sp_category
(cost=0.00..331.00 rows=3339 width=6)
Filter: ((category_id = 1142) OR
(category_id =
1143) OR (category_id = 1145) OR
(category_id =
1259) OR (category_id = 1161)) ->
Index Scan using approved_profiles_id_key on approved_profiles a (cost=0.00..5.30
rows=1
width=15) Index Cond: ((a.id = "outer".id) AND (a.source = "outer".source))
(24 rows)
tradein_clients=#
>
>
> Hmmm... upgrade to 7.3.1 was not that smooth..
> after upgrade i could not run a single query..
>
> tradein_clients=> SELECT * from hogs;
> ERROR: current transaction is aborted, queries ignored until end of transaction
>block
> tradein_clients=>
> any other query seems to be giving the same ERROR.
>
> check the message below on psql start (7.3.1) with a 7.3.1 server.
>
> PS: i applied the heir patch though ... ;-) will try again without that.
>
> [postgres@ns3 postgres]$ psql -Upostgres -h66.234.10.12 tradein_clients
>
>>> ERROR: nodeRead: did not find '}' at end of plan node
>
> Welcome to psql 7.3.1, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
> \h for help with SQL commands
> \? for help on internal slash commands
> \g or terminate with semicolon to execute query
> \q to quit
>
> tradein_clients=>
>
>
>
>
> regds
> mallah.
>
>
> On Monday 03 February 2003 09:15 pm, Tom Lane wrote:
>> "Rajesh Kumar Mallah." <[EMAIL PROTECTED]> writes:
>> > I think if i do that i will hve to immediately upgrade
>> > all the 7.3.0 clients in other machines to 7.3.1 rite?
>>
>> No.
>>
>> regards, tom lane
>
> --
>
>
> -
[SQL] float and numeric in 7.3
Hi I've just upgraded Postgresql 7.2 -> 7.3 Some of my views use function "round" with datatype "float". They don't work after upgrading: ERROR: Function round(double precision, integer) does not exist What happened? 7.3 can't automaticaly cast float into numeric? Or maybe 7.2 had both functions round(numeric, integer) and round(double precision, integer)? Should I change float into numeric in my tables if possible? Regards, Tomasz Myrta ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] SQL99/SQL92 Grammar
Does anyone have a definitive BNF grammar of SQL99 or SQL92? (I'd prefer 99 but I'll take what I can get ;) I'm trying to make a simplified XML to SQL translator, and I need the grammar to do so (not Postgres's grammar...the standard grammar). It must be available somewhere, because Postgres developers need to refer to it at some point... If the grammar is accompanied with explanations (like Postgres does in its online manual, or like Oracle in theirs), that would be bestbut again, I'll take what I can get. I tried looking on the ANSI website, but with no results. Damn bastards...how can they make a standard and then charge for you to see it? What the hell is the point if the 'standard' is not open to everyone who might need it? What about the students and joe-blows in this world (like me) who don't work for a company and who don't have a ton of money to spend on stupid ANSI subscriptions.? Oh sorry...I got off on a rant Regardless, I'd love the grammar or a link to where I can view it for _free_. Can anyone help a guy out? Thanks! Les Hazlewood ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] pg_views
hi, I'm trying to write some code that can analyze the database structure and i need a way to discover the composition of a view (the tables and table.column info). I've managed to do much of this by querying the pg_views for the definition and literally parsing the SQL myself, but obviously that has many limitations unless I impliment a complete SQL parser and that's beyond the scope of what I want :-) I'm wondering if PostgreSQL actually reparses the view definition on each invocation or if it stores the required information in some accessible place. My goal is to take a view name as input and output the tables and columns composing the view. thanks in advance, - lex -- Lex Berezhny <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Format in psql
> man psql. > > Inside psql: > \pset border 2 > Fine, \pset border 2 draws the border. But it donot format the multi line value, without affecting the format of other column. I want to format the multi line column appropriately. It should not affect the format of other column, similar to perl format ($ man perlform). Hope i am clear. regards, bhuvaneswaran ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] iceberg queries
Does PostgreSQL optimizer handle iceberg queries well? Thanks Wei ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
