Postgres Pro
Downloads
Home   >   mailing lists

Thread: plpgsql FOUND Variable

plpgsql FOUND Variable

From
Alex
Date:
Hi,
when I run a SELECT INTO and the result is 0 or zero row, then I still
get a positive result if
using IF FOUND THEN....

Is there a problem or do we need to use ROW_COUNT instead ?

Alex

Re: plpgsql FOUND Variable

From
Tom Lane
Date:
Alex <alex@meerkatsoft.com> writes:
> when I run a SELECT INTO and the result is 0 or zero row, then I still
> get a positive result if
> using IF FOUND THEN....

I recall Neil Conway fixed some problems with FOUND a version or two
back.  If you are not on 7.3.*, update.  If you are, let's see the
details.

            regards, tom lane

Re: plpgsql FOUND Variable

From
Alex
Date:
Tom,
I am using 7.3.4

The problem

_tmpRec = RECORD;
_name   = VARCHAR;

SELECT INTO _tmpRec * FROM address WHERE name = _name AND status = ''1''
AND last_name NOTNULL
IF FOUND THEN
  RETURN ''found'';
ELSE ....

Above Query does not produce any results. Executed in psql  result = 0
rows. However I do get the return string "found" back.

Alex

Tom Lane wrote:

>Alex <alex@meerkatsoft.com> writes:
>
>
>>when I run a SELECT INTO and the result is 0 or zero row, then I still
>>get a positive result if
>>using IF FOUND THEN....
>>
>>
>
>I recall Neil Conway fixed some problems with FOUND a version or two
>back.  If you are not on 7.3.*, update.  If you are, let's see the
>details.
>
>            regards, tom lane
>
>
>
>

Re: plpgsql FOUND Variable

From
Tom Lane
Date:
Alex <alex@meerkatsoft.com> writes:
> SELECT INTO _tmpRec * FROM address WHERE name = _name AND status = ''1''
> AND last_name NOTNULL
> IF FOUND THEN
>   RETURN ''found'';
> ELSE ....

> Above Query does not produce any results.

The above query produces a syntax error, because you're missing a
semicolon after the SELECT INTO.  If you want help with this, you'll
need to offer an exact example rather than an approximation.

I have just looked at the source code and verified that exec_stmt_select
sets FOUND appropriately, so I'm pretty certain that you've made some
trivial pilot error or other.  But without exact details on what you
did, it's unlikely anyone else will guess the mistake.

            regards, tom lane

Re: plpgsql FOUND Variable

From
"Nigel J. Andrews"
Date:
On Thu, 28 Aug 2003, Alex wrote:

> Hi,
> when I run a SELECT INTO and the result is 0 or zero row, then I still
> get a positive result if
> using IF FOUND THEN....

Works for me in 7.3.x. What are you using? Are you perhaps doing something
like:
    select count(*) from mytable

which will of course find something even if there are no rows in the
table.


--
Nigel Andrews

Arrays and Indices / Foreign Keys

From
Alex
Date:
Hi,
I am using Arrays to store a list of values. Is it possible to

a) index array fields
b) to create a foreign key constraint from the array

Thanks
Alex

>
>
>

Re: Arrays and Indices / Foreign Keys

From
Joe Conway
Date:
Alex wrote:
> a) index array fields

It is possible in 7.4 beta, but not before.

> b) to create a foreign key constraint from the array

Same answer. In 7.4:

# CREATE TABLE t1 (id int[] PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey"
for table "t1"
CREATE TABLE
# INSERT INTO t1 VALUES(ARRAY[1,2,3]);
INSERT 2028925 1
# CREATE TABLE t2 (id int, fk int[] REFERENCES t1(id));
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE TABLE
# INSERT INTO t2 VALUES(1, ARRAY[1,2,3]);
INSERT 2028935 1
# INSERT INTO t2 VALUES(2, ARRAY[3,4,5]);
ERROR:  insert or update on "t2" violates foreign key constraint "$1"
DETAIL:  Key (fk)=({3,4,5}) is not present in "t1".

Joe

Re: Arrays and Indices / Foreign Keys

From
Alex
Date:
Joe, that is good news.
When will be 7.4 availbable?

Also,
what i actually wanted  is to ckeck that if  lets say ARRAY[1,2,3]
 is inserted but 1,2 or 3 (the the entire array) is not  present in the
reference table.
Will that be available too ?

Alex


Joe Conway wrote:

> Alex wrote:
>
>> a) index array fields
>
>
> It is possible in 7.4 beta, but not before.
>
>> b) to create a foreign key constraint from the array
>
>
> Same answer. In 7.4:
>
> # CREATE TABLE t1 (id int[] PRIMARY KEY);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "t1_pkey" for table "t1"
> CREATE TABLE
> # INSERT INTO t1 VALUES(ARRAY[1,2,3]);
> INSERT 2028925 1
> # CREATE TABLE t2 (id int, fk int[] REFERENCES t1(id));
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE TABLE
> # INSERT INTO t2 VALUES(1, ARRAY[1,2,3]);
> INSERT 2028935 1
> # INSERT INTO t2 VALUES(2, ARRAY[3,4,5]);
> ERROR:  insert or update on "t2" violates foreign key constraint "$1"
> DETAIL:  Key (fk)=({3,4,5}) is not present in "t1".
>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>

Re: Arrays and Indices / Foreign Keys

From
Joe Conway
Date:
Alex wrote:
> Joe, that is good news.
> When will be 7.4 availbable?

Beta2 is just starting. There isn't a firm date for the 7.4 release that
I'm aware of, but start looking for it in mid-September.

> Also,
> what i actually wanted  is to ckeck that if  lets say ARRAY[1,2,3]
> is inserted but 1,2 or 3 (the the entire array) is not  present in the
> reference table.
> Will that be available too ?

I was afraid that's what you were after. I think the answer is no, at
least not with standard RI constraints. You might be able to do
something with a custom trigger though.

Joe