[SQL] Text cast problem

2009-04-03 Thread Volkmar Herbst
Dear all- I encountered the following problem: select * from parcel where number = '255 ' gives me 1 row but select * from parcel where number = '255 ' ::text does give me 0 rows. The cast removes the trailing empty character. Why is that? Unfortunately the statements are generate

Re: [SQL] Text cast problem

2009-04-03 Thread Adrian Klaver
On Thursday 02 April 2009 2:51:30 am Volkmar Herbst wrote: > Dear all- > > I encountered the following problem: > > > > select * from parcel where number = '255 ' > > gives me 1 row but > > > > select * from parcel where number = '255 ' ::text > > does give me 0 rows. The cast removes the trailing

Re: [SQL] FUNCTION problem

2009-04-03 Thread Adrian Klaver
On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: > > > Now I remember. Its something that trips me up, the RECORD in RETURN setof > RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for > a better explanation- > http://www.postgresql.org/docs/8.3/interactive/plpgsql-d

Re: [SQL] FUNCTION problem

2009-04-03 Thread Adrian Klaver
On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote: > On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: > > Now I remember. Its something that trips me up, the RECORD in RETURN > > setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See > > below for a better explanation

Re: [SQL] FUNCTION problem

2009-04-03 Thread Peter Willis
Adrian Klaver wrote: On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote: On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: Now I remember. Its something that trips me up, the RECORD in RETURN setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for a better

Re: [SQL] FUNCTION problem

2009-04-03 Thread Adrian Klaver
- "Peter Willis" wrote: > Adrian Klaver wrote: > > On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote: > >> On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: > >>> Now I remember. Its something that trips me up, the RECORD in > RETURN > >>> setof RECORD is not the same thing as t

Re: [SQL] Performance problem with row count trigger

2009-04-03 Thread Tony Cebzanov
Greg Sabino Mullane wrote: > A few things spring to mind: > > 1) Use a separate table, rather than storing things inside of > dataset itself. This will reduce the activity on the dataset table. A separate table just for that one column? Would that really help, given that I'd have to add the fore

Re: [SQL] FUNCTION problem

2009-04-03 Thread Peter Willis
Adrian Klaver wrote: If you are using Postgres 8.1+ then it becomes even easier because you can use OUT parameters in the function argument list to eliminate the "as test(c1 int,c2 int)" clause. At this point it becomes a A-->B-->C problem i.e determine what your inputs are, how you want to

[SQL] pl/pgsql or control structures outside of a function?

2009-04-03 Thread Peter Koczan
Hi all, Is there any way to use PL/pgSQL code outside of a function? The reason I'm asking is that I'm porting some code from sybase/isql/SQR, and it allows some control code structures to be used in an sql script. For instance, begin if ((select count(*) from users where login = 'foo') = 0)

Re: [SQL] pl/pgsql or control structures outside of a function?

2009-04-03 Thread John DeSoi
On Apr 3, 2009, at 5:03 PM, Peter Koczan wrote: Is there any way to use PL/pgSQL code outside of a function? No. The reason I'm asking is that I'm porting some code from sybase/isql/SQR, and it allows some control code structures to be used in an sql script. For instance, CASE might wor