Обсуждение: plpgsql FOUND Variable

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

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

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
>
>
>
>

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

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

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

>
>
>

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

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
>
>

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