Search code examples
functionpostgresqlvariablesrecordrule

Selecting Columns From a Record Variable (PostgreSQL 8.4)

I'm trying to select a column from a record variable in a function I'm calling from an Update rule and am getting the following error:

'could not identify column "name" in record data type'

The following is what I'm doing to produce the error:

From within an Update rule:

SELECT * INTO TEMPORARY TABLE TempTable FROM NEW;
SELECT MyFunction();

From within MyFunction()

DECLARE RecordVar Record;
SELECT * INTO STRICT RecordVar FROM TempTable;
EXECUTE 'UPDATE AnotherTable SET column = $1.name' USING RecordVar;

Note: I realise that there are easier ways to achieve what the above code is achieving but I've simplified the actual implementation to focus on the problem I'm having, which has opened up other possible solutions but I'd really like to get the above code working if possible.

Solution

  • I just figured it out. Rather than inserting the columns from NEW into the Temporary Table, I insert the NEW record as a single column into the Temporary Table and refer to it as RecordVar."NEW" inside my function. My rule and function now look like this:

    From within an Update rule:

    SELECT NEW AS "NEW" INTO TEMPORARY TABLE TempTable;
SELECT MyFuction();

    From within MyFunction()

    DECLARE RecordVar Record;
SELECT * INTO STRICT RecordVar FROM TempTable;
EXECUTE 'UPDATE AnotherTable SET column = $1.name' USING RecordVar."NEW";