Re: [SQL] Possible Bug regarding temp tables (sql or psql?)
Any idea why the table can't be seen with \d in psql then? Christopher Kings-Lynne <[EMAIL PROTECTED]> [02/07/04 00:21]: > > No - they go away at the end of a _connection_. However, there is now a > patch floating around on -hackers that would add an ' ON COMMIT DROP;' > option to CREATE TEMP TABLE. -- In the event of an emergency, my ass can be used as a flotation device. - Bender ---(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] how to write procedures
Hi, I am using postgre sql server on linux server but for my database I am using storedprocedures which i need to create , but there are no commands to create procedures it says it does not support is there any way to work with stored procedures in postgre sql server. thanks, srikanth. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Can this be done with sql?
In my database i have values recorded in one minute intervals. I would like a query that can get me results for other time intervals. For example - return maximum value in each 3 minute interval. Any ideas how i can do this with sql? I tried writing a procedure in plsql but i am told it does not support tuples as output. I can get the all the one minute intervals and process them to get me three minute intervals in my application but i would rather not do the expensive call for the one minute intervals in the first place due to the large number of data. any ideas? thanks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Possible Bug regarding temp tables (sql or psql?)
Mark Frazer wrote: > Any idea why the table can't be seen with \d in psql then? It is a known problem with temp tables. They aren't visible with \d because they are invisible system tables that are removed on exit. I think 7.3 will fix this. > > Christopher Kings-Lynne <[EMAIL PROTECTED]> [02/07/04 00:21]: > > > > No - they go away at the end of a _connection_. However, there is now a > > patch floating around on -hackers that would add an ' ON COMMIT DROP;' > > option to CREATE TEMP TABLE. > > -- > In the event of an emergency, my ass can be used as a flotation > device. - Bender > > > > ---(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 > > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] how to write procedures
On Thu, 4 Jul 2002, srikanth wrote: You are looking for functions :) Study them a bit and then you will feel grateful for PostgreSQL! > Hi, I am using postgre sql server on linux server but for my database I am > using storedprocedures which i need to create , but there are no commands to > create procedures it says it does not support is there any way to work with > stored procedures in postgre sql server. > thanks, > srikanth. > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] how to write procedures
Hi , Stored procedures are supported in pgsql for quite a long time consult postgresql docs on website http://www.postgresql.org/idocs/index.php?xplang.html or your local installations. regds malz. On Thursday 04 July 2002 16:15, srikanth wrote: > Hi, I am using postgre sql server on linux server but for my database I am > using storedprocedures which i need to create , but there are no commands > to create procedures it says it does not support is there any way to work > with stored procedures in postgre sql server. > thanks, > srikanth. > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Can this be done with sql?
Hi , you can use GROUP BY , at the expense of adding one more column of SERIAL data type, say, select * from t_a limit 10; access_log=# SELECT * from t_a limit 15; sno | value -+--- 1 | 4533 2 | 2740 3 | 9970 4 | 6445 5 | 2220 6 | 2301 7 | 6847 8 | 5739 9 | 5286 10 | 5556 11 | 9309 12 | 9552 13 | 8589 14 | 5935 15 | 2382 (15 rows) if you want avg for every third item you can use: access_log=# SELECT avg(value) from t_a group by (1+(sno-1)/3) limit 5; avg - 5747.67 3655.33 5957.33 8139.00 5635.33 (5 rows) you can replace 3 in the SQL with any number for grouping that many records. if you need MEAN , STDDEV , MAX, MIN etc you can use approprite AGGREGATE that PGSQL supports for numbers eg for MAX access_log=# SELECT MAX(value) from t_a group by (1+(sno-1)/3) limit 5; max -- 9970 6445 6847 9552 8589 (5 rows) Regds MAlz. On Thursday 04 July 2002 00:02, teknokrat wrote: > In my database i have values recorded in one minute intervals. I would > like a query that can get me results for other time intervals. For > example - return maximum value in each 3 minute interval. Any ideas > how i can do this with sql? I tried writing a procedure in plsql but i > am told it does not support tuples as output. I can get the all the > one minute intervals and process them to get me three minute intervals > in my application but i would rather not do the expensive call for the > one minute intervals in the first place due to the large number of > data. any ideas? > > thanks > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] how to write procedures
On Thu, 4 Jul 2002, Rajesh Kumar Mallah. wrote: Just to add stored procedures in pgsql are implemented thru functions. Popular languages are pg/plsql and "C". > > Hi , > > Stored procedures are supported in pgsql for > quite a long time > > consult postgresql docs on website > http://www.postgresql.org/idocs/index.php?xplang.html > > or your local installations. > > regds > malz. > > > > On Thursday 04 July 2002 16:15, srikanth wrote: > > Hi, I am using postgre sql server on linux server but for my database I am > > using storedprocedures which i need to create , but there are no commands > > to create procedures it says it does not support is there any way to work > > with stored procedures in postgre sql server. > > thanks, > > srikanth. > > > > > > > > > > ---(end of broadcast)--- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Boolean to int
Hi, I'd like to get 1 or 0 from a select on a boolean field. How could I do ? I tried the following rule, which I can't insert : CREATE FUNCTION bool_to_int( boolean ) RETURNS integer AS ' DECLARE my_bool ALIAS FOR $1 ; BEGIN -- Retourne 1 si true, 0 si false IF my_bool IS FALSE THEN RETURN 0 ; ELSIF my_bool IS TRUE THEN RETURN 1 ; ELSE RETURN -1 ; END IF ; END ; ' LANGUAGE 'plpgsql'; CREATE RULE boolean_return AS ON SELECT TO DOCUMENT DO INSTEAD SELECT document_id, workflow_id, type_document_id, image_id, theme_id, document_version, document_surtitre, document_titre, document_chapeau, document_synthese, document_corps, document_pdf, document_date_creation, document_mot_clef, (bool_to_int(document_online)) as document_online, bool_to_int(document_valid) as document_valid FROM document; psql:cnambo_proc_stock.sql:69: ERROR: select rule's target entry 15 has different type from attribute document_online Who could help me ? S@S -- _Stéphane SCHILDKNECHT___ | AurorA-SAS 69-71, Av. Pierre Grenier 92100 BOULOGNE | | Tel : 01.58.17.03.20 Fax : 01.58.17.03.21 | | mailto:[EMAIL PROTECTED] - ICQ : 142504394 | | "Free Markets have taught that innovation is best when | | ideas flow freely." Adam Smith | |_| ---(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] Boolean to int
On 4 Jul 2002, Stephane Schildknecht wrote: > CREATE RULE boolean_return AS ON SELECT TO DOCUMENT DO INSTEAD > SELECT > document_id, > workflow_id, > type_document_id, > image_id, > theme_id, > document_version, > document_surtitre, > document_titre, > document_chapeau, > document_synthese, > document_corps, > document_pdf, > document_date_creation, > document_mot_clef, > (bool_to_int(document_online)) as document_online, > bool_to_int(document_valid) as document_valid FROM document; > > > psql:cnambo_proc_stock.sql:69: ERROR: select rule's target entry 15 > has different type from attribute document_online You might have better luck defining a view on Document that does it. Select rules are fairly limited except as the apply to making views work. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
