SEO

#1 (**permalink**) 04-08-2008, 09:29 PM

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#2 (**permalink**) 04-08-2008, 09:29 PM

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Error defining composite types and returning ROWTYPEs