From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marinos Yannikos <mjy@geizhals.at>
Cc: pgsql-sql@postgresql.org
Subject: Re: plpgsql: PERFORM vs. SELECT INTO (PERFORM not setting FOUND variable?)
Date: Mon, 25 Oct 2004 16:02:27 -0400
Message-ID: <23025.1098734547@sss.pgh.pa.us> (raw)
In-Reply-To: <417D57B1.8010507@geizhals.at>
References: <417D57B1.8010507@geizhals.at>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, mjy@geizhals.at
Subject: Re: plpgsql: PERFORM vs. SELECT INTO (PERFORM not setting FOUND variable?)
In-Reply-To: <23025.1098734547@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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