Thread: [PLPGSQL] PERFORM into an EXECUTE allowed ?
Hello !
I'm trying to build a PL/PGSQL function trigger.
When the function is triggered, PostgreSQL complains about an error at
or near PEFORM.
Here's the piece of code :
EXECUTE 'PERFORM COUNT(*)'
|| ' FROM ' || quote_ident(TG_RELNAME)
|| ' GROUP BY ' || quote_ident(column_name_to_check)
|| ' HAVING COUNT(*) > 1';
The next lines of code don't check for the value (so, PERFORM), but just
do some action IF FOUND. Table and column to check are dynamic and comes
from the trigger, that's why I use EXECUTE.
Is it allowed to do a PERFORM into an EXECUTE ? I didn't see any notes
about such limitation in the doc, but I ask in case of... :-/
Many thanks in advance !
Regards,
--
Bruno Baguette - bruno.baguette@gmail.com
Bruno Baguette <bruno.baguette@gmail.com> writes:
> EXECUTE 'PERFORM COUNT(*)'
> || ' FROM ' || quote_ident(TG_RELNAME)
> || ' GROUP BY ' || quote_ident(column_name_to_check)
> || ' HAVING COUNT(*) > 1';
PERFORM is a plpgsql keyword, not a SQL keyword, so it's not surprising
that this fails. What I'm wondering is what exactly you hope the above
will accomplish? Why would you want to execute this query only to have
the results discarded?
regards, tom lane
Le 27/10/08 05:16, Tom Lane a écrit : > Bruno Baguette <bruno.baguette@gmail.com> writes: >> EXECUTE 'PERFORM COUNT(*)' >> || ' FROM ' || quote_ident(TG_RELNAME) >> || ' GROUP BY ' || quote_ident(column_name_to_check) >> || ' HAVING COUNT(*) > 1'; > > PERFORM is a plpgsql keyword, not a SQL keyword, so it's not surprising > that this fails. What I'm wondering is what exactly you hope the above > will accomplish? Why would you want to execute this query only to have > the results discarded? > > regards, tom lane Hello Tom ! I only want to know if there is at least one result for this query. I don't want to get the results itself. That's why I wanted to use PERFORM and working with IF FOUND THEN (or) IF NOT FOUND THEN. Since this trigger is intented to be usable for any table and any column name, this query is dynamically builed into the trigger. I've read on <http://www.postgresql.org/docs/8.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS> that the statement type for my need was PERFORM (since SELECT INTO is not supported currently on EXECUTE). That's why I was trying to do a PERFORM into an EXECUTE. Indeed, you're right, PERFORM is PL/PLGSQL keyword, not SQL keyword. Mmmh, I wonder how I will workaround that problem... Regards, -- Bruno Baguette - bruno.baguette@gmail.com
Bruno Baguette <bruno.baguette@gmail.com> writes:
> Le 27/10/08 05:16, Tom Lane a �crit :
>> Bruno Baguette <bruno.baguette@gmail.com> writes:
>>> EXECUTE 'PERFORM COUNT(*)'
>>> || ' FROM ' || quote_ident(TG_RELNAME)
>>> || ' GROUP BY ' || quote_ident(column_name_to_check)
>>> || ' HAVING COUNT(*) > 1';
>>
>> PERFORM is a plpgsql keyword, not a SQL keyword, so it's not surprising
>> that this fails. What I'm wondering is what exactly you hope the above
>> will accomplish? Why would you want to execute this query only to have
>> the results discarded?
> I only want to know if there is at least one result for this query.
> I don't want to get the results itself.
In that case you just do EXECUTE 'SELECT ...
regards, tom lane
Le 27/10/08 13:12, Tom Lane a écrit : > In that case you just do EXECUTE 'SELECT ... > > regards, tom lane Hem... I feel quite stupid, but you're (again) right. It now run perfectly. By the way, I said previously that EXECUTE does not allow to put the results into a target. I was wrong again. (cf. <http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html>, chapter "38.5.4. Executing Dynamic Commands"). EXECUTE command-string [ INTO [STRICT] target ]; If I can make a suggestion, I think it could be interesting to complete the doc with an additionnal example with such a EXECUTE that put results in a target. Thanks for the light ! :-) Regards, -- Bruno Baguette