[SQL] Conversion from Sybase ASA
In sybase I can use the following syntax: select 'T' as src,(if tbon is null or tbon < '1900/12/12' or tbon > '1900/9/30' then 'U' else 'P' endif) as pres from ftrans this returns two columns: src = '' and pres which has either the value 'U' or 'P' how would I express this in postgresql? thanks Richard
Re: [SQL] Conversion from Sybase ASA
select 'T' as src,(if tbon is null or tbon < '1900/12/12' or tbon > '1900/9/30' then 'U' else 'P' endif) as pres from ftrans select 'T' as src,(CASE WHEN tbon is null or tbon < '1900/12/12' or tbon > '1900/9/30' THEN 'U' ELSE 'P' END) as pres from ftrans I think it works in Oracle too :) Pay attention at tbon < '1900/12/12'. Try to use conversion on date formats like to_date() or to_timestamp(). Good luck - Original Message - From: Richard Sydney-Smith To: [EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 10:06 AM Subject: [SQL] Conversion from Sybase ASA In sybase I can use the following syntax: select 'T' as src,(if tbon is null or tbon < '1900/12/12' or tbon > '1900/9/30' then 'U' else 'P' endif) as pres from ftrans this returns two columns: src = '' and pres which has either the value 'U' or 'P' how would I express this in postgresql? thanks Richard
Re: [SQL] Conversion from Sybase ASA
On Tuesday 09 September 2003 09:06, Richard Sydney-Smith wrote: > In sybase I can use the following syntax: > > select 'T' as src,(if tbon is null or tbon < '1900/12/12' or tbon > > '1900/9/30' then 'U' else 'P' endif) as pres from ftrans > > this returns two columns: src = 'T' and pres which has either the value 'U' > or 'P' > > how would I express this in postgresql? "CASE": http://www.postgresql.org/docs/7.3/static/functions-conditional.html Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] MINUS & ROWNUM in PostGres
> The problem is we are using PostGres 7.1.In this version REPLACE() is not > available. It sounded like you were just starting to work on the change over. I highly suggest upgrading to 7.3 at the very least, 7.4 if you're going to be a few months prior to going to production. A ton of good work has been put into PostgreSQL in the last couple of years. Anyway, you'll need to write your own replace() if you stick with that release. signature.asc Description: This is a digitally signed message part
Re: [SQL] undefine currval()
On Mon, 8 Sep 2003, Tom Lane wrote: > "scott.marlowe" <[EMAIL PROTECTED]> writes: > > On Mon, 8 Sep 2003, Bruce Momjian wrote: > >> I don't know how you could have an application that doesn't know if it > >> has issued a nextval() in the current connection. Unless you can explain > >> that, we have no intention of playing tricks with currval() for > >> connection pooling. > > > Actually, I would think the very act of using connection pooling would > > ensure that applications may well not know whether or not a nextval had > > been called. > > The point is that it's not very sensible to be using currval except > immediately after a nextval --- usually in the same transaction, I would > think. I'm pretty sure my second paragraph agreed with you on that. > Certainly, not resetting currval implies that there is > *potential* coupling between different transactions that happen to share > a connection. But ISTM that such coupling would represent a bug in the > application. And that one too. > Chris said he was using currval being undefined to know that no rows > were inserted, but this seems less than compelling to me (why not look > at the results of the insert commands you used?). I'd support adding a > currval-reset feature if someone can make a more compelling argument why > a connection-pooling application would need it. I'd say that if someone is looking at that, it would be better to have some kind of reset_connection call that makes a connection look like you just established it. Bit I'd never use it. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] contrib/ltree
how do i get jdbc to recognize the ltree type that comes with the contrib/ltree extension? This: Object object = resultSet.getObject(columnNumber); generates the following exception: Exception caused by: No class found for ltree at org.postgresql.jdbc1.AbstractJdbc1Connection.getObject(AbstractJdbc1Connecti on.java:693) at org.postgresql.jdbc2.AbstractJdbc2Connection.getObject(AbstractJdbc2Connecti on.java:117) at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getObject(AbstractJdbc2ResultSet .java:147) ... Regards, Floyd Shackelford 4 Peaks Technology Group, Inc. VOICE: 334.735.9428 FAX: 702.995.6462 EMAIL: [EMAIL PROTECTED] ICQ #: 161371538 PGP Key ID: 0x2E84F2F2 PGP Fone available on request at private.fwshackelford.com Shackelford Motto: ACTA NON VERBA - Actions, not words Alabama State Motto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our Rights The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf "We have allowed our constitutional republic to deteriorate into a virtually unchecked direct democracy. Today's political process is nothing more than a street fight between various groups seeking to vote themselves other people's money. Individual voters tend to support the candidate that promises them the most federal loot in whatever form, rather than the candidate who will uphold the rule of law." --Rep. Ron Paul ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Test, please ignore
Just a teste, please ignore ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] How can I optimize this query
I have the following query UPDATE accumulator1 SET accumulator1.status = "User Excluded" WHERE accumulator1.name NOT IN (SELECT DISTINCT accumulator1.name FROM accumulator1, diaaffectedstmts WHERE diaaffectedstmts.stmt like '*'+ accumulator1.name +'*' and diaaffectedstmts.xref_type <> "D"); How can I optimize this query? Thanks Jain ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] MINUS & ROWNUM in PostGres
On Thu, 4 Sep 2003, Rod Taylor wrote: > > 2 Also I need to find an alternative for ROWNUM in oracle.. > > If you are looking for a unique identifier, try using the OID. > ROWID is oracle's unique identifier, ROWNUM is the row number in a query result. In the past it has been suggested that ROWNUM could be replaced by the nextval of a temporary sequence. Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] MINUS & ROWNUM in PostGres
Hi, Do we have any replacement for REPLACE() of Oracle in PostGres? I know we can function to do same job. Regards Jomon -Original Message- From: Kris Jurka [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 1:52 PM To: Rod Taylor Cc: Jomon Skariah; [EMAIL PROTECTED] Subject: Re: [SQL] MINUS & ROWNUM in PostGres On Thu, 4 Sep 2003, Rod Taylor wrote: > > 2 Also I need to find an alternative for ROWNUM in oracle.. > > If you are looking for a unique identifier, try using the OID. > ROWID is oracle's unique identifier, ROWNUM is the row number in a query result. In the past it has been suggested that ROWNUM could be replaced by the nextval of a temporary sequence. Kris Jurka ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] MINUS & ROWNUM in PostGres
Hi, Thanx for your reply. The problem is we are using PostGres 7.1.In this version REPLACE() is not available. Regards Jomon -Original Message- From: Rod Taylor [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 7:41 PM To: Jomon Skariah Cc: Kris Jurka; [EMAIL PROTECTED] Subject: RE: [SQL] MINUS & ROWNUM in PostGres On Mon, 2003-09-08 at 09:44, Jomon Skariah wrote: > Hi, > > Do we have any replacement for REPLACE() of Oracle in PostGres? What does replace() do? String replacement? http://www.postgresql.org/docs/7.3/interactive/functions-string.html replace(string text, from text, to text) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] How can I optimize this query
On Tue, Sep 09, 2003 at 13:39:10 +0530, Jainendra Kumar P <[EMAIL PROTECTED]> wrote: > I have the following query > > UPDATE accumulator1 SET accumulator1.status = "User Excluded" > WHERE accumulator1.name NOT IN > (SELECT DISTINCT accumulator1.name > FROM accumulator1, diaaffectedstmts > WHERE diaaffectedstmts.stmt like '*'+ accumulator1.name +'*' and > diaaffectedstmts.xref_type <> > "D"); > > How can I optimize this query? It will probably work better in 7.4. But for now you might try replacing NOT IN with NOT EXISTS. As long as accumulator1.name isn't going to be NULL you should be able to rewrite the subselect to find rows where name in the subselect matches name in the outer select. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [SQL] plpgsql doesn't coerce boolean expressions to boolean
Tom Lane wrote: > Manfred Koizar <[EMAIL PROTECTED]> writes: > > On Mon, 08 Sep 2003 11:40:32 -0400, Tom Lane <[EMAIL PROTECTED]> > > wrote: > >> 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans > >> will be accepted in exactly the same cases where they'd be accepted > >> in a boolean-requiring SQL construct (such as CASE). (By default, > >> none are, so this isn't really different from #2. But people could > >> create casts to boolean to override this behavior in a controlled > >> fashion.) > > > I vote for 4. > > I'm willing to do that. OK, what release should we do this? -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] plpgsql doesn't coerce boolean expressions to boolean
On Mon, 08 Sep 2003 11:40:32 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: >4. Use the parser's coerce_to_boolean procedure, so that nonbooleans > will be accepted in exactly the same cases where they'd be accepted > in a boolean-requiring SQL construct (such as CASE). (By default, > none are, so this isn't really different from #2. But people could > create casts to boolean to override this behavior in a controlled > fashion.) I vote for 4. And - being fully aware of similar proposals having failed miserably - I propose to proceed as follows: If the current behaviour is considered a bug, let i=4, else let i=5. In 7.i: Create a new GUC variable "plpgsql_strict_boolean" (silly name, I know) in the "VERSION/PLATFORM COMPATIBILITY" section of postgresql.conf. Make the new behaviour dependent on this variable. Default plpgsql_strict_boolean to false. Place a warning into the release notes and maybe into the plpgsql documentation. In 7.j, j>i: Change the default value of plpgsql_strict_boolean to true. Issue WARNINGs or NOTICEs as appropriate. Update documentation. In 7.k, k>j: Remove old behaviour and GUC variable. Update documentation. Servus Manfred ---(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] plpgsql doesn't coerce boolean expressions to boolean
Manfred Koizar <[EMAIL PROTECTED]> writes: > On Mon, 08 Sep 2003 11:40:32 -0400, Tom Lane <[EMAIL PROTECTED]> > wrote: >> 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans >> will be accepted in exactly the same cases where they'd be accepted >> in a boolean-requiring SQL construct (such as CASE). (By default, >> none are, so this isn't really different from #2. But people could >> create casts to boolean to override this behavior in a controlled >> fashion.) > I vote for 4. I'm willing to do that. > And - being fully aware of similar proposals having > failed miserably - I propose to proceed as follows: > If the current behaviour is considered a bug, let i=4, else let i=5. > In 7.i: Create a new GUC variable "plpgsql_strict_boolean" (silly > name, I know) in the "VERSION/PLATFORM COMPATIBILITY" section of > postgresql.conf. Make the new behaviour dependent on this variable. > Default plpgsql_strict_boolean to false. Place a warning into the > release notes and maybe into the plpgsql documentation. > In 7.j, j>i: Change the default value of plpgsql_strict_boolean to > true. Issue WARNINGs or NOTICEs as appropriate. Update > documentation. > In 7.k, k>j: Remove old behaviour and GUC variable. Update > documentation. I'm not willing to do that much work for what is, in the greater scheme of things, a tiny change. If we did that for every user-visible change, our rate of forward progress would be a mere fraction of what it is. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] plpgsql doesn't coerce boolean expressions to boolean
Define the language! If it breaks code, so be it. 2. Throw an error if the _expression_ doesn't return boolean. Yes, yes, absolutely. By definition "an IF, WHILE, or EXIT statement is a boolean _expression_" SO if "some stupid piece of text" THEN should not compile, there is no BOOLEAN _expression_. C's implementation of hat is true and false has always, IMHO, been hideous. But then again, I am a Pascal kind of thinker. An integer with a value of 1 is still only an integer, IF I <> 0 THEN ... is clear and un-ambiguous. Tom Lane wrote: Following up this gripe http://archives.postgresql.org/pgsql-sql/2003-09/msg00044.php I've realized that plpgsql just assumes that the test _expression_ of an IF, WHILE, or EXIT statement is a boolean _expression_. It doesn't take any measures to ensure this is the case or convert the value if it's not the case. This seems pretty bogus to me. However ... with the code as it stands, for pass-by-reference datatypes any nonnull value will appear TRUE, while for pass-by-value datatypes any nonzero value will appear TRUE. I fear that people may actually be depending on these behaviors, particularly the latter one which is pretty reasonable if you're accustomed to C. So while I'd like to throw an error if the argument isn't boolean, I'm afraid of breaking people's function definitions. Here are some possible responses, roughly in order of difficulty to implement: 1. Leave well enough alone (and perhaps document the behavior). 2. Throw an error if the _expression_ doesn't return boolean. 3. Try to convert nonbooleans to boolean using plpgsql's usual method for cross-type coercion, ie run the type's output proc to get a string and feed it to bool's input proc. (This seems unlikely to avoid throwing an error in very many cases, but it'd be the most consistent with other parts of plpgsql.) 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans will be accepted in exactly the same cases where they'd be accepted in a boolean-requiring SQL construct (such as CASE). (By default, none are, so this isn't really different from #2. But people could create casts to boolean to override this behavior in a controlled fashion.) Any opinions about what to do? 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] [HACKERS] plpgsql doesn't coerce boolean expressions to boolean
Tom Lane wrote: Following up this gripe http://archives.postgresql.org/pgsql-sql/2003-09/msg00044.php I've realized that plpgsql just assumes that the test expression of an IF, WHILE, or EXIT statement is a boolean expression. It doesn't take any measures to ensure this is the case or convert the value if it's not the case. This seems pretty bogus to me. However ... with the code as it stands, for pass-by-reference datatypes any nonnull value will appear TRUE, while for pass-by-value datatypes any nonzero value will appear TRUE. I fear that people may actually be depending on these behaviors, particularly the latter one which is pretty reasonable if you're accustomed to C. So while I'd like to throw an error if the argument isn't boolean, I'm afraid of breaking people's function definitions. Here are some possible responses, roughly in order of difficulty to implement: 1. Leave well enough alone (and perhaps document the behavior). 2. Throw an error if the expression doesn't return boolean. ERROR is the cleanest way, but I'd vote for conversion to boolean to keep the damage within reason. Jan 3. Try to convert nonbooleans to boolean using plpgsql's usual method for cross-type coercion, ie run the type's output proc to get a string and feed it to bool's input proc. (This seems unlikely to avoid throwing an error in very many cases, but it'd be the most consistent with other parts of plpgsql.) 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans will be accepted in exactly the same cases where they'd be accepted in a boolean-requiring SQL construct (such as CASE). (By default, none are, so this isn't really different from #2. But people could create casts to boolean to override this behavior in a controlled fashion.) Any opinions about what to do? 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]) -- #==# # 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [HACKERS] plpgsql doesn't coerce boolean expressions to boolean
Jan Wieck <[EMAIL PROTECTED]> writes: > ERROR is the cleanest way, but I'd vote for conversion to boolean to > keep the damage within reason. Which style of conversion did you like? These were the choices: >> 3. Try to convert nonbooleans to boolean using plpgsql's usual method >> for cross-type coercion, ie run the type's output proc to get a >> string and feed it to bool's input proc. (This seems unlikely to >> avoid throwing an error in very many cases, but it'd be the most >> consistent with other parts of plpgsql.) >> >> 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans >> will be accepted in exactly the same cases where they'd be accepted >> in a boolean-requiring SQL construct (such as CASE). (By default, >> none are, so this isn't really different from #2. But people could >> create casts to boolean to override this behavior in a controlled >> fashion.) At this point I'm kinda leaning to #4, because (for example) people could create a cast from integer to boolean to avoid having to fix their plpgsql functions right away. #3 would not offer any configurability of behavior. regards, tom lane ---(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] plpgsql doesn't coerce boolean expressions to boolean
I would suggest to throw a error, or at least a warning. This will FORCE people to program in the correct way. I also thought that 'IF $1 THEN ...' should work ok but giving it a other thought it's indeed stuped to write that way (I'm from the C world...) Ries -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Tom Lane Verzonden: maandag 8 september 2003 17:41 Aan: [EMAIL PROTECTED]; [EMAIL PROTECTED] Onderwerp: [SQL] plpgsql doesn't coerce boolean expressions to boolean Following up this gripe http://archives.postgresql.org/pgsql-sql/2003-09/msg00044.php I've realized that plpgsql just assumes that the test expression of an IF, WHILE, or EXIT statement is a boolean expression. It doesn't take any measures to ensure this is the case or convert the value if it's not the case. This seems pretty bogus to me. However ... with the code as it stands, for pass-by-reference datatypes any nonnull value will appear TRUE, while for pass-by-value datatypes any nonzero value will appear TRUE. I fear that people may actually be depending on these behaviors, particularly the latter one which is pretty reasonable if you're accustomed to C. So while I'd like to throw an error if the argument isn't boolean, I'm afraid of breaking people's function definitions. Here are some possible responses, roughly in order of difficulty to implement: 1. Leave well enough alone (and perhaps document the behavior). 2. Throw an error if the expression doesn't return boolean. 3. Try to convert nonbooleans to boolean using plpgsql's usual method for cross-type coercion, ie run the type's output proc to get a string and feed it to bool's input proc. (This seems unlikely to avoid throwing an error in very many cases, but it'd be the most consistent with other parts of plpgsql.) 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans will be accepted in exactly the same cases where they'd be accepted in a boolean-requiring SQL construct (such as CASE). (By default, none are, so this isn't really different from #2. But people could create casts to boolean to override this behavior in a controlled fashion.) Any opinions about what to do? 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 ---(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] [HACKERS] plpgsql doesn't coerce boolean expressions to boolean
Tom Lane wrote: Following up this gripe http://archives.postgresql.org/pgsql-sql/2003-09/msg00044.php I've realized that plpgsql just assumes that the test expression of an IF, WHILE, or EXIT statement is a boolean expression. It doesn't take any measures to ensure this is the case or convert the value if it's not the case. This seems pretty bogus to me. However ... with the code as it stands, for pass-by-reference datatypes any nonnull value will appear TRUE, while for pass-by-value datatypes any nonzero value will appear TRUE. I fear that people may actually be depending on these behaviors, particularly the latter one which is pretty reasonable if you're accustomed to C. So while I'd like to throw an error if the argument isn't boolean, I'm afraid of breaking people's function definitions. Here are some possible responses, roughly in order of difficulty to implement: 1. Leave well enough alone (and perhaps document the behavior). 2. Throw an error if the expression doesn't return boolean. 3. Try to convert nonbooleans to boolean using plpgsql's usual method for cross-type coercion, ie run the type's output proc to get a string and feed it to bool's input proc. (This seems unlikely to avoid throwing an error in very many cases, but it'd be the most consistent with other parts of plpgsql.) 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans will be accepted in exactly the same cases where they'd be accepted in a boolean-requiring SQL construct (such as CASE). (By default, none are, so this isn't really different from #2. But people could create casts to boolean to override this behavior in a controlled fashion.) Any opinions about what to do? It won't bite me so maybe I don't have a right to express an opinion :-) plpgsql is not C - it appears to be in the Algol/Pascal/Ada family, which do tend to avoid implicit type conversion. On that basis, option 2 seems like it might be the right answer and also the one most likely to break lots of existing functions. Maybe the right thing would be to deprecate relying on implicit conversion to boolean for one release cycle and then make it an error. cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [HACKERS] plpgsql doesn't coerce boolean expressions to boolean
Tom Lane wrote: Jan Wieck <[EMAIL PROTECTED]> writes: ERROR is the cleanest way, but I'd vote for conversion to boolean to keep the damage within reason. Which style of conversion did you like? These were the choices: 3. Try to convert nonbooleans to boolean using plpgsql's usual method for cross-type coercion, ie run the type's output proc to get a string and feed it to bool's input proc. (This seems unlikely to avoid throwing an error in very many cases, but it'd be the most consistent with other parts of plpgsql.) 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans will be accepted in exactly the same cases where they'd be accepted in a boolean-requiring SQL construct (such as CASE). (By default, none are, so this isn't really different from #2. But people could create casts to boolean to override this behavior in a controlled fashion.) At this point I'm kinda leaning to #4, because (for example) people could create a cast from integer to boolean to avoid having to fix their plpgsql functions right away. #3 would not offer any configurability of behavior. Agreed - #4. Thinking of the problem about deprication of features and transition time, it would be nice for this kind of compatibility breaking changes to have a _per database_ config option that controls old vs. new behaviour, wouldn't it? Don't know exactly how you'd like that to be. Maybe with a pg_config catalog that inherits default settings from template1 but can then be changed in every database. This would even include the possibility to *switch* one single prod database back to the old behaviour in case the supposedly cleaned up application isn't as clean as supposed to. Jan -- #==# # 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 8: explain analyze is your friend
Re: [SQL] [GENERAL] plPGSQL bug in function creation
Marek Lewczuk wrote:
Hello,
I think that there is a bug in plPGSQL - or maybe I don't know something
about this language. Try to create this function
Ok., this is the function created in plPGSQL:
CREATE FUNCTION "public"."test" (text, text) RETURNS text AS'
BEGIN
IF $1 THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
If you will execute SELECT test('tess', 'erer') -> then "tess" will be
returned. If you will execute: SELECT test(NULL, 'buuu'); -> then it
will return NULL, but it should return "buuu". I tried to figure out why
it is happening so i modifye this function to this:
CREATE FUNCTION "public"."test" (text, text) RETURNS text AS'
BEGIN
RETURN 'test';
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
And when i execute: SELECT test(NULL, 'buuu'); -> it returns me NULL
value, when it should return "buuu". Well I think that something is
wrong here.
If I will modify this function again to this:
CREATE FUNCTION "public"."test" (varchar, varchar) RETURNS text AS'
BEGIN
IF $1 THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Everything is working OK.. So the problem is in TEXT type definition.
I'm using PG 7.3.1 on Win/Cyg
---(end of broadcast)---
TIP 8: explain analyze is your friend
You can only test for NULL with 'IS NULL'.
NULL is NOT:
FALSE, 0, or F
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
