pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql: PERFORM vs. SELECT INTO (PERFORM not setting FOUND variable?)
3+ messages / 3 participants
[nested] [flat]
* plpgsql: PERFORM vs. SELECT INTO (PERFORM not setting FOUND variable?)
@ 2004-10-25 19:44 Marinos Yannikos <mjy@geizhals.at>
0 siblings, 2 replies; 3+ messages in thread
From: Marinos Yannikos @ 2004-10-25 19:44 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm probably misintepreting the documented equivalence of PERFORM and
SELECT INTO as far as the special variable FOUND is concerned, but the
following 2 definitions do not seem to produce the same result.
create table blup ( t1 text, t2 text );
create function blup_unique2 (text,text) returns boolean as 'begin
perform (select 1 from blup where t1=$1 or t1=$2 or t2=$1 or t2=$2 or
$1=$2 limit 1); return NOT FOUND; end' LANGUAGE plpgsql;
create function blup_unique3 (text,text) returns boolean as 'declare x
record; begin select into x 1 from blup where t1=$1 or t1=$2 or t2=$1 or
t2=$2 or $1=$2 limit 1; return NOT FOUND; end' LANGUAGE plpgsql;
The first will always produce false, i.e. apparently the subquery used
by PERFORM will not set the variable FOUND as expected. Is this correct?
(btw.: I'm trying a few ways to ensure that all values in both t1 and t2
are unique:
alter table blup add constraint check (blup_unique3(t1,t2));
- perhaps there are more elegant ways, any suggestions?)
Regards,
Marinos
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: plpgsql: PERFORM vs. SELECT INTO (PERFORM not setting FOUND variable?)
@ 2004-10-25 20:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marinos Yannikos <mjy@geizhals.at>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2004-10-25 20:02 UTC (permalink / raw)
To: Marinos Yannikos <mjy@geizhals.at>; +Cc: pgsql-sql
Marinos Yannikos <mjy@geizhals.at> writes:
> create function blup_unique2 (text,text) returns boolean as 'begin
> perform (select 1 from blup where t1=$1 or t1=$2 or t2=$1 or t2=$2 or
> $1=$2 limit 1); return NOT FOUND; end' LANGUAGE plpgsql;
You've got a syntax problem. PERFORM is syntactically like SELECT,
so what you wrote is equivalent to
SELECT (SELECT 1 FROM blup ....)
In other words, you are evaluating a scalar subquery, which is going to
return either "1" or "NULL" depending on whether the WHERE matches,
or give an error if the WHERE matches multiple rows (a case you wouldn't
hit because of the LIMIT). So the outer SELECT produces exactly one row
containing the scalar result, and FOUND always ends up TRUE.
So what you want is just
PERFORM 1 FROM blup ...
and then check the FOUND result from that.
(The 8.0 docs hopefully explain this more clearly; PERFORM was
certainly not very well documented before.)
Note that I'm concerned that the performance of this will suck ...
in particular you really ought to test the $1=$2 case separately.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: plpgsql: PERFORM vs. SELECT INTO (PERFORM not setting FOUND
@ 2004-10-25 20:08 Edmund Bacon <ebacon@onesystem.com>
parent: Marinos Yannikos <mjy@geizhals.at>
1 sibling, 0 replies; 3+ messages in thread
From: Edmund Bacon @ 2004-10-25 20:08 UTC (permalink / raw)
To: Marinos Yannikos <mjy@geizhals.at>; +Cc: pgsql-sql
Marinos Yannikos wrote:
> (btw.: I'm trying a few ways to ensure that all values in both t1 and t2
> are unique:
> alter table blup add constraint check (blup_unique3(t1,t2));
> - perhaps there are more elegant ways, any suggestions?)
>
No doubt someone will tell me this is Very Wrong:
create table blup_text(txt text primary key);
create table blup (t1 text references blup_text(txt),
t2 text references blup_text(txt));
You may want to add NOT NULL conditions to t1, t2.
If your text values are long, this may be a good place to use synthetic
keys - e.g.
create table blup_text(id serial primary key, txt text unique);
create table blup(t1 integer references blup_text(id),
t2 integer references blub_text(id));
--
Edmund Bacon <ebacon@onesystem.com>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-10-25 20:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-25 19:44 plpgsql: PERFORM vs. SELECT INTO (PERFORM not setting FOUND variable?) Marinos Yannikos <mjy@geizhals.at>
2004-10-25 20:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-25 20:08 ` Re: plpgsql: PERFORM vs. SELECT INTO (PERFORM not setting FOUND Edmund Bacon <ebacon@onesystem.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox