Re: [SQL] dynmic column names inside trigger?
On Nov 23, 2007, at 12:06 , Louis-David Mitterrand wrote: On Wed, Nov 21, 2007 at 09:14:14AM +0100, Bart Degryse wrote: LANGUAGE plpgsql VOLATILE RETURNS NULL ON NULL INPUT; Hi, I was curious as to why you created this function with a prototype of func(intext IN text, outtext OUT text) ... returns NULL Don't confuse RETURNS NULL ON NULL INPUT with the function return value: this is the verbose spelling of STRICT. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] dynmic column names inside trigger?
On Wed, Nov 21, 2007 at 09:14:14AM +0100, Bart Degryse wrote: > I would do something like this (not tested, but conceptually working): > > CREATE or replace FUNCTION sanitize_text(webtext IN text, cleantext OUT text) > AS > $body$ > BEGIN > cleantext = translate(webtext, E'\x92\x96', '''-'); > cleantext = regexp_replace(cleantext, E'\x9c', 'oe', 'g'); > cleantext = regexp_replace(cleantext, E'\x85', '...', 'g'); > END; > $body$ > LANGUAGE plpgsql VOLATILE RETURNS NULL ON NULL INPUT; Hi, I was curious as to why you created this function with a prototype of func(intext IN text, outtext OUT text) ... returns NULL instead of the usual func(intext text) ... returns TEXT Is that a more efficient way? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] PL argument max size, and doubt
Tom Lane escribió: Martin Marques <[EMAIL PROTECTED]> writes: Tom Lane escribió: Martin Marques <[EMAIL PROTECTED]> writes: I have always heard that modification queries should be EXECUTED in PL. AFAICR. Run far away from whatever source gave you that advice... Sorry, it was with DDL commands. That's not much better ;-). DDL commands don't have plans, so there's not anything that could be invalidated. I don't see any reason to use an EXECUTE unless there's an actual textual change in the command you need to execute. Well, actually http://archives.postgresql.org/pgsql-sql/2007-02/msg00214.php See the follow-ups ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Aleatory exception
Sorry for the previous incomplete text. I add the followings: For instance I got that exception for the following statement string: '"my_function"( NULL, ''TEXT1'', NULL::int4, NULL::int4, 5413, , TIMESTAMP WITH TIME ZONE ''2007-11-23 10:08:29.904+02'', 19255, 0 )' What is wrong with this statement ? Regards, Sabin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Aleatory exception
Hi there, I have a custom function where I execute a dynamic statement. I trap the exceptions, and sometimes I got the following error data: SQLSTATE = 22P02, SQLERRM = invalid input syntax for integer: "NULL::int4" For instance I got this for the following statement string: Please explain what could be the reason ? TIA, Sabin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Aleatory exception - found cause
Hi, Finally I found the cause of the problem. I found deeper inside my calls, a function which has to return an integer column from a RECORD rec variable. The rec is assigned with the following statement: EXECUTE 'SELECT pk AS "PK" FROM "my_table" WHERE my_where_clause' INTO rec. When my_where_clause is not fulfilled, rec is initialized somehow with null. The exception is rised at the end where I call: RETURN rec."PK" Maybe someone can explain this behavior. TIA, Sabin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
