Selecting Columns From a Record Variable (PostgreSQL 8.4)

Question

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.

user1545610 · Accepted Answer · 2012-07-25 00:16:49Z

4

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";

answered Jul 25, 2012 at 0:16

user1545610

811 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erwin Brandstetter · Accepted Answer · 2012-07-23 13:06:50Z

2

The second part could work like this:

DO
$BODY$
DECLARE
   RecordVar TempTable;
BEGIN
   SELECT * INTO STRICT RecordVar FROM TempTable LIMIT 1;
   EXECUTE 'UPDATE AnotherTable  SET column = $1.name'
   USING RecordVar;
END;
$BODY$

Note how I use the table name as type. PostgreSQL automatically creates a composite type for every table in the system.

A variable holds one row, the SELECT can return many rows. All but the first will be discarded. I added LIMIT 1 to clarify the effect. I doubt that is what you want.

You probably shouldn't have to use a temporary table in a rule to begin with. You may want to post your complete setup ...

answered Jul 23, 2012 at 13:06

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

3 Comments

user1545610 Over a year ago

Hi Erwin, thanks so much for your reply. I'm confident your suggestion would work for one case, but my problem is that I have multiple views, each with their own underlying table that I want to apply my function to. If I were to specify the table as the type for RecordVar, I'd have to create a function for each table type, which I'd like to avoid. Sorry for not including this information in my original question.

user1545610 Over a year ago

With regards to my use of temporary tables, I'm simply using them as a mechanism to exchange NEW and OLD from within the rule to my function because you can't pass the data type record to a function. If there is a better way to pass NEW and OLD from a rule to a function, doing so might result in a solution to my problem. One thing I don't understand is why I can use NEW.<column name> in the context of a rule or trigger knowing that NEW is of the record data type but not RecordVar.<column name>, which I've populated from an instance of NEW.

user1545610 Over a year ago

I've tried combinations of the following, which resulted in 'record type has not been registered' or 'cannot cast type record to <table>': EXECUTE 'UPDATE AnotherTable SET column = $1.name' USING RecordVar::Table; EXECUTE 'UPDATE AnotherTable SET column = $1.name' USING ROW(RecordVar.*)::Table; I thought the following would work but it didn't work either: CREATE TEMPORARY TABLE YetAnotherTable (Like Table); INSERT INTO YetAnotherTable (SELECT * FROM TempTable); SELECT * FROM YetAnotherTable INTO RecordVar; EXECUTE 'UPDATE AnotherTable SET column = $1.name' USING RecordVar::YetAnotherTable;

Collectives™ on Stack Overflow

Selecting Columns From a Record Variable (PostgreSQL 8.4)

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related