[SQL] Calling Functions in RULEs

[Jan B.]

Hello,

I would like to call multiple PL/pgSQL procedures (i.e. functions 
returning void) from INSERT, UPDATE and DELETE RULEs.


Using "SELECT some_procedure();" is a possible way to do that, but it 
causes a dummy result table to be delivered to the application invoking 
the INSERT/UPDATE/DELETE. When using multiple SELECTs to call multiple 
functions inside the RULE, multiple result tables will be delivered to 
the application, which is very unhandy and causes trouble especially 
when using asynchronous command processing. Though "psql" does only show 
the last result table, all dummy result tables have to be fetched by 
PGgetResult(), when using asynchronous mode with libpq.


Is there any tidy way to call a void-function inside a RULE without 
creating a result table that is passed to the application?
Using a command like "UPDATE dummy_table SET dummy=1 WHERE 
some_procedure() NOTNULL;" would work, but that would be very dirty.


Does a command like "PERFORM" (from PG/pgSQL) exist in SQL too, which 
discards the (void) result of the function call?



Thanks for helping

Jan Behrens
||

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}

[Jan B.]

Markus Bertheau wrote:

Hi,

why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of
ARRAY[] resp. '{}'?

Markus



Perhaps Arrays always have to contain at least one element? (I don't 
know for sure.)


SELECT array[];
ERROR:  syntax error at or near "]" at character 14
LINE 1: SELECT array[];

SELECT array[1];
 array
---
 {1}
(1 row)

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [SQL] Transaction in plpgslq

[Jan B.]

Rafa Couto wrote:

2005/5/20, Andrew Hammond <[EMAIL PROTECTED]>:



The solution to your problem is locking (or concurrency control if you
prefer). While we're at it, we might as well optimize your statement a
little too using ORDER BY with LIMIT instead of min().

SELECT id INTO _contacto_id
FROM contactos
WHERE contactos.operadora_id IS NULL
 AND contactos.actividad_id > = _actividad_id
ORDER BY id LIMIT 1
FOR UPDATE;

Take a look at the "FOR UPDATE" section of the SELECT description for an
explanation of how this works.




I understand "FOR UPDATE" clause is locking while is selecting rows
only. It does not secure atomic execution from 'SELECT' to 'UPDATE' in
next statement. Is not it?




After the SELECT FOR UPDATE command locked some rows, other concurrent 
changes to the database could be made, but changes, which require to 
lock that rows will be deferred.


The lock will be hold until the end of the transaction (that means at 
least until the function returns).


---(end of broadcast)---
TIP 8: explain analyze is your friend