Tag Info

New answers tagged

0

Your attempt fails for multiple reasons. First of all a row is not an array. This construct is just not possible: FOR field IN NEW LOOP ... But there is more. I am not sure I like the general idea. This kind of trigger would run for at least every statement (don't use a row-level trigger for this!), which is quite a bit of overhead. It is also ...


1

Merlin Moncure from the PostgreSQL General mailing list has an answer: The SQL standard mandates that rows containing all null values satisfy 'IS NULL = true'. However, postgres internally has nullibitily of container types that is distinct from their contents. I personally find this to be a good thing for various reasons but the facts are that ...


2

There are several things wrong with this trigger. First: your delete statement. You can't compare NULL using =. You need to use IS NULL: DELETE FROM test2 WHERE email IS NULL; Second: a trigger function (quote from the manual) "must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for." So return ...


0

http://www.postgresql.org/docs/9.1/static/installation-platform-notes.html See section 15.7.6.2 Looks like you can import bash into plpgsql. I know SQL or ingres sql is compatible with unix. Can't find anything to suggest that plpgsql is. HTH


1

There is a semicolon missing after the DO statement: ... END -- semicolon is not necessary here $do$; -- but here it is COMMIT; The first example works, because the semicolon is optional at the end of a script. That's also why the semicolon after END inside the block is optional.


1

I have re-tested your function and it seems to work as advertised. If, in fact a duplicate value was entered, you would get an exception that would roll back the transaction. The function is designed to trap such an exception (UNIQUE_VIOLATION) and to try again, which would find the (now) existing row and return the existing id. Your report of ...


2

I assumed you were using a pl/pgsql block (probably a function). For the generic case, you can use record, it can basically take a row of any structure, often used like DECLARE i record; BEGIN FOR i IN SELECT this, that, something, else FROM some_table LOOP ... END LOOP; There is also a possibility of defining a view: CREATE VIEW ...



Top 50 recent answers are included