plpgsql FOUND Variable

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:

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

Alex,

I had similar problem with pgsql 7.3.3 and 7.3.4 when
using SELECT INTO RECORD variable. I noticed that
FOUND is always set to TRUE, but if no result is found,
RECORD variable is filled with NULL values.

My workaround was to check for NULL field value,
if there should not be ever NULL (primary key, etc).

---
Zygmuntowicz Michal

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:

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