Thread: Error defining composite types and returning ROWTYPEs
CREATE TABLE test_table ( some_column integer, another_column text ); CREATE TYPE test_type as ( some_type test_table.some_column%TYPE ); Gives me: ERROR: syntax error at or near "%" at character 62 LINE 2: some_type test_table.some_column%TYPE I also can't define rowtypes as return types on functions: CREATE OR REPLACE FUNCTION get_test_table_single() RETURNS test_table%ROWTYPE AS $$ DECLARE v_single test_table%ROWTYPE; BEGIN SELECT * INTO v_single FROM test_table LIMIT 1; RETURN v_single; END; $$ LANGUAGE plpgsql; Gives me: ERROR: syntax error at or near "%" at character 65 LINE 2: RETURNS test_table%ROWTYPE AS $$ Can somebody tell me what I'm doing wrong? This is driving me insane. Geoff
Geoffrey Giesemann <geoffwa@iinet.net.au> writes: > CREATE TYPE test_type as ( > some_type test_table.some_column%TYPE > ); > Gives me: > ERROR: syntax error at or near "%" at character 62 > LINE 2: some_type test_table.some_column%TYPE > I also can't define rowtypes as return types on functions: > CREATE OR REPLACE FUNCTION get_test_table_single() > RETURNS test_table%ROWTYPE AS $$ %ROWTYPE is a plpgsql-ism, not a SQL construct. %TYPE isn't a SQL construct either --- AFAIR we only support it in the very limited context of CREATE FUNCTION's argument and result type declarators. regards, tom lane