[SQL] Disable trigger with Postgresql 7.4.x?
Hello, I read in an old thread http://archives.postgresql.org/pgsql-hackers/2002-08/msg00079.php ... that starting with Postgresql 7.3, there is a new way to disable and enable triggers in Postgresql. The "old" way was: update pg_class set reltriggers=0 where relname = 'YOUR_TABLE_NAME'; update pg_class set reltriggers = count(*) from pg_trigger where pg_class.oid=tgrelid and relname='YOUR_TABLE_NAME'; What is the recommended way of doing that under Postgresql 7.4.x? Thanks Philippe Lang ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] stored procedures and type of returned result.
*** Aucun virus n'a été détecté dans la pièce-jointe no filename --- No virus was detected in the attachment no filename Votre courrier a été inspecté par InterScan Messaging Security Suite --- Your mail has been scanned by InterScan MSS. *** Hello, I'm new in PostgreSQL. It's very cool. I would like to know how to return a set of records from a stored procedure. For example, i would like to execute these sql command from a stored procedure : select t.typnum, t.typcom, t.typcateg, s.symurlgraph from structure.type t left join structure.symbole s on t.typcode = s.typcode; Is it possible ? Do i have to return results in an array ? If yes, how could i do ? Do you think using plPHP mays help me ? Regards, Patrice OLIVER. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] stored procedures and type of returned result.
Patrice OLIVER wrote: Hello, I'm new in PostgreSQL. It's very cool. Hello, and yes it is isn't it :-) I would like to know how to return a set of records from a stored procedure. For example, i would like to execute these sql command from a stored procedure : select t.typnum, t.typcom, t.typcateg, s.symurlgraph from structure.type t left join structure.symbole s on t.typcode = s.typcode; Broadly speaking you'll want something like: CREATE TYPE my_return_type AS ( a integer, b text ); CREATE FUNCTION my_function(integer) RETURNS SETOF my_return_type AS ' SELECT foo_a, foo_b FROM foo WHERE foo_c = $1; ' LANGUAGE SQL; You don't need to define your own type if you want to return the same columns as a table, you can use the table-name instead. For more complex cases where you need procedural code, you probably want to read Stephan Szabo's set-returning-functions article on techdocs. http://techdocs.postgresql.org/guides/SetReturningFunctions HTH -- Richard Huxton Archonet Ltd ---(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] stored procedures and type of returned result.
Patrice, You might have a look at "views" as well. That's not strictily speaking a stored procedure, but maybe it's what you are searching for? Philippe Lang -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Richard Huxton Envoyé : mardi, 24. août 2004 11:26 À : Patrice OLIVER Cc : [EMAIL PROTECTED] Objet : Re: [SQL] stored procedures and type of returned result. Patrice OLIVER wrote: > Hello, > > I'm new in PostgreSQL. It's very cool. Hello, and yes it is isn't it :-) > I would like to know how to return a set of records from a stored > procedure. > > For example, i would like to execute these sql command from a stored > procedure : > > select t.typnum, t.typcom, t.typcateg, s.symurlgraph from structure.type t > left join structure.symbole s > on t.typcode = s.typcode; Broadly speaking you'll want something like: CREATE TYPE my_return_type AS ( a integer, b text ); CREATE FUNCTION my_function(integer) RETURNS SETOF my_return_type AS ' SELECT foo_a, foo_b FROM foo WHERE foo_c = $1; ' LANGUAGE SQL; You don't need to define your own type if you want to return the same columns as a table, you can use the table-name instead. For more complex cases where you need procedural code, you probably want to read Stephan Szabo's set-returning-functions article on techdocs. http://techdocs.postgresql.org/guides/SetReturningFunctions HTH -- Richard Huxton Archonet Ltd ---(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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] stored procedures and type of returned result.
Patrice OLIVER wrote: Hello, I got this error message : ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "lsttable" line 5 at return next You need to treat set-returning functions as though they are tables. SELECT * FROM lsttable(); NOT SELECT lsttable(); -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Disable trigger with Postgresql 7.4.x?
Philippe Lang wrote: > Hello, > > I read in an old thread > > http://archives.postgresql.org/pgsql-hackers/2002-08/msg00079.php > > ... that starting with Postgresql 7.3, there is a new way to disable and > enable triggers in Postgresql. The "old" way was: > > update pg_class set reltriggers=0 where relname = 'YOUR_TABLE_NAME'; > update pg_class set reltriggers = count(*) from pg_trigger where > pg_class.oid=tgrelid and relname='YOUR_TABLE_NAME'; > > What is the recommended way of doing that under Postgresql 7.4.x? Same. We haven't added a better way yet. -- 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Possible rounding error of large float values?
Title: Possible rounding error of large float values? When setting a float column to this value: 9223372036854775807 It gets selected out as: 9.22337203685478E18 Which appears to be rounded. When we cast it to numeric type we get: 922337203685478 Which also is rounded. It is still possible to find the row using the original value select * from where = 9223372036854775807 Is this expected behavior? Thanks, Adam
Re: [SQL] Possible rounding error of large float values?
Adam, > 9223372036854775807 > > It gets selected out as: > > 9.22337203685478E18 This is a property of FLOAT data types. They round. > Which appears to be rounded. When we cast it to numeric type we get: > > 922337203685478 > > Which also is rounded. It is still possible to find the row using the > original value Hmmm ... is 15 digits the limit of NUMERIC? It may be. > Is this expected behavior? Yes. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Possible rounding error of large float values?
On Tue, 2004-08-24 at 20:52, Josh Berkus wrote: > Adam, > > > 9223372036854775807 > > > > It gets selected out as: > > > > 9.22337203685478E18 > > This is a property of FLOAT data types. They round. > > > Which appears to be rounded. When we cast it to numeric type we get: > > > > 922337203685478 > > > > Which also is rounded. It is still possible to find the row using the > > original value > > Hmmm ... is 15 digits the limit of NUMERIC? It may be. It must be the limit of float. Numeric can't produce any more than it was given and it was cast from float. bray=# select 653596708775675750507850507570708696432 ::numeric; numeric - 653596708775675750507850507570708696432 (1 row) -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "I saw in the night visions, and, behold, one like the Son of man came with the clouds of heaven, and came to the Ancient of days, and they brought him near before him. And there was given him dominion, and glory, and a kingdom, that all people, nations, and languages, should serve him; his dominion is an everlasting dominion, which shall not pass away, and his kingdom that which shall not be destroyed." Daniel 7:13,14 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Possible rounding error of large float values?
On 8/24/2004 4:21 PM, Oliver Elphick wrote: On Tue, 2004-08-24 at 20:52, Josh Berkus wrote: Adam, > 9223372036854775807 > > It gets selected out as: > > 9.22337203685478E18 This is a property of FLOAT data types. They round. > Which appears to be rounded. When we cast it to numeric type we get: > > 922337203685478 > > Which also is rounded. It is still possible to find the row using the > original value Hmmm ... is 15 digits the limit of NUMERIC? It may be. The original numeric code I presented contained even log(), ln() and trigonometric functions that produced results of up to 1,000 digits precision. They wheren't considered usefull, so they didn't survive, but numeric is that good after all. Jan It must be the limit of float. Numeric can't produce any more than it was given and it was cast from float. bray=# select 653596708775675750507850507570708696432 ::numeric; numeric - 653596708775675750507850507570708696432 (1 row) -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Possible rounding error of large float values?
(B# select 9223372036854775807 = 9223372036854775807::float; (B ?column? (B-- (B t (B(1 row) (B (B# select 9223372036854775807 = 9223372036854775807.0::float; (B ?column? (B-- (B t (B(1 row) (B (B# select 9223372036854775807 = 9223372036854775807.::numeric; (B ?column? (B-- (B t (B(1 row) (B (BThis and the fact that it's still possible to find the row using the (Boriginal value would seem to indicate that the rounding is just a display (Bartifact.. (B (B- Original Message - (BFrom: "Oliver Elphick" <[EMAIL PROTECTED]> (BTo: "Josh Berkus" <[EMAIL PROTECTED]> (BCc: "Adam Lancaster" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> (BSent: Wednesday, August 25, 2004 5:21 AM (BSubject: Re: [SQL] Possible rounding error of large float values? (B (B (B> On Tue, 2004-08-24 at 20:52, Josh Berkus wrote: (B> > Adam, (B> > (B> > > 9223372036854775807 (B> > > (B> > > It gets selected out as: (B> > > (B> > > 9.22337203685478E18 (B> > (B> > This is a property of FLOAT data types. They round. (B> > (B> > > Which appears to be rounded. When we cast it to numeric type we get: (B> > > (B> > > 922337203685478 (B> > > (B> > > Which also is rounded. It is still possible to find the row using the (B> > > original value (B> > (B> > Hmmm ... is 15 digits the limit of NUMERIC? It may be. (B> (B> It must be the limit of float. Numeric can't produce any more than it (B> was given and it was cast from float. (B> (B> bray=# select 653596708775675750507850507570708696432 ::numeric; (B> numeric (B> - (B> 653596708775675750507850507570708696432 (B> (1 row) (B> (B> -- (B> Oliver Elphick [EMAIL PROTECTED] (B> Isle of Wight http://www.lfix.co.uk/oliver (B> GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA (B> (B> "I saw in the night visions, and, behold, one like the (B> Son of man came with the clouds of heaven, and came to (B> the Ancient of days, and they brought him near before (B> him. And there was given him dominion, and glory, and (B> a kingdom, that all people, nations, and languages, (B> should serve him; his dominion is an everlasting (B> dominion, which shall not pass away, and his kingdom (B> that which shall not be destroyed." (B> Daniel 7:13,14 (B> (B> (B> ---(end of broadcast)--- (B> TIP 4: Don't 'kill -9' the postmaster (B (B (B---(end of broadcast)--- (BTIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Possible rounding error of large float values?
On Wed, 25 Aug 2004, Iain wrote: > > # select 9223372036854775807 = 9223372036854775807::float; > ?column? > -- > t > (1 row) > This and the fact that it's still possible to find the row using the > original value would seem to indicate that the rounding is just a display > artifact.. I don't think that's true since the number ending in 807 is equal to a version ending in 808. sszabo=# select 9223372036854775807 = 9223372036854775808::float; ?column? -- t (1 row) (In this next one I'm casting both because I want to choose the types of both literals) sszabo=# select 9223372036854775807::float = 9223372036854775808::numeric; ?column? -- t (1 row) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Possible rounding error of large float values?
> I don't think that's true since the number ending in 807 is equal to a > version ending in 808. > > sszabo=# select 9223372036854775807 = 9223372036854775808::float; > ?column? > -- > t > (1 row) Good point. I think that the problems with using floats are well documented (I particulary like the doc that come with IBMs java.lang.BigDecimal package) so there shouldn't be any excuse for being surprised by how they behave. Basically floats are fine when used for what they were desgned for, but a liability otherwise. Just thinking about it now, I can't remember last time I designed a database that used float data... regards Iain ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
