Skip site navigation (1) Skip section navigation (2)

Inserting a record returned from a function

From: p9e883002(at)sneakemail(dot)com
To: pgsql-novice(at)postgresql(dot)org
Subject: Inserting a record returned from a function
Date: 2008-04-16 07:46:54
Message-ID: 18407-81584@sneakemail.com (view raw or flat)
Thread:
Lists: pgsql-novice
I have a function defined as:

CREATE OR REPLACE FUNCTION word_doc_from_raw(character varying, character 
varying, bytea)
RETURNS record AS
$BODY$

SELECT ROW(
id_from_word( $1 ),
id_from_doc_name( $2 ),
$3
)

$BODY$
LANGUAGE 'sql' STABLE;

And I'm trying to INSERT the returned record into a table defined as:

CREATE TABLE word_doc
(
word_id integer NOT NULL,
doc_id integer NOT NULL,
posns bytea,
CONSTRAINT word_doc_pkey PRIMARY KEY (word_id, doc_id),
CONSTRAINT word_doc_word_id_fkey FOREIGN KEY (word_id)
REFERENCES words (word_id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;

with the statement:

insert into word_doc ( word_id, doc_id, posns )
values( (word_doc_from_raw( 'word', 'document.name', 'some raw data' )).* )

and getting the error:

ERROR: record type has not been registered
SQL state: 42809

Does this mean I must always define a type in order to do anything useful with a 
record returned from a function?
I've seen references in the docs to "casting" and a syntax like:

ROW( ...)::tag

Is this useful in this situation?

Cheers, buk.





Responses

pgsql-novice by date

Next:From: Sean DavisDate: 2008-04-16 11:04:24
Subject: Re: Importing normalised data by SQL script in remote DB
Previous:From: AndreasDate: 2008-04-16 03:01:42
Subject: Re: Importing normalised data by SQL script in remote DB

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group