Declare variable of composite type in PostgreSQL using %TYPE

Question: How can I declare a variable of the same type a parameter in a stored function?

The simple answer is use %TYPE, this works:

CREATE OR REPLACE FUNCTION test_function_1(param1 text)
  RETURNS integer AS
$BODY$ 
DECLARE
    myVariable param1%TYPE;
BEGIN
    return 1;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

But the problem is when param1 is a composite type:

CREATE TYPE comp_type as
(
    field1 text
)

CREATE OR REPLACE FUNCTION test_function_2(param1 comp_type)
  RETURNS integer AS
$BODY$ 
DECLARE
    myVariable param1%TYPE;
BEGIN
    return 1;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
开发者_StackOverflow社区

This doesn't work:

ERROR: type comp_type does not exist [SQL State=42704]

So how can I do when param1 is a composite type?

(Note: Just myVariable comp_type is not a good option because my function is slightly more complex.)

---

Edited: I had a mistake on copy&paste, the real error is:

ERROR: invalid type name "param1%TYPE"
  Position: 130 [SQL State=42601] 

And using param1%ROWTYPE the error is:

ERROR: relation "param1" does not exist
  Where: compilation of PL/pgSQL function "test_function_2" near line 3 [SQL State=42P01] 

---

Use %ROWTYPE in that case.

Simple case

Tests by A.H. and DavidEG have shown this won't work. Interesting problem!
You could try a workaround. As long as your definition is like the example you can simply resort to

CREATE FUNCTION test(param1 comp_type)
  RETURNS integer
  LANGUAGE plpgsql VOLATILE AS
$func$ 
DECLARE
   myvar comp_type;
BEGIN
   RETURN 1;
END
$func$;

But your real problem is probably not as simple?

The real problem

As expected, the real problem turns out to be more complex: a polymorphic input type.
Workaround for that scenario was harder, but should work flawlessly:

CREATE FUNCTION test(param1 anyelement, OUT a integer, OUT myvar anyelement)
  RETURNS record
  LANGUAGE plpgsql VOLATILE AS
$func$
BEGIN
   myvar := $1;    -- myvar has the required type now.
   -- do stuff with myvar.

   myvar := NULL;  -- reset if you don't want to output ..
   a := 1;
END;
$func$;

Call:

SELECT a FROM test('("foo")'::comp_type); -- just retrieve a, ignore myvar

See full output:

SELECT * FROM test('("foo")'::comp_type);

Note for Postgres 9.0+

There has been a crucial update in Postgres 9.0. The release notes:

• Allow input parameters to be assigned values within PL/pgSQL functions (Steve Prentice)

Formerly, input parameters were treated as being declared CONST, so the function's code could not change their values. This restriction has been removed to simplify porting of functions from other DBMSes that do not impose the equivalent restriction. An input parameter now acts like a local variable initialized to the passed-in value.

In addition to my workaround, you can (ab)use input variables directly now.

Dynamic field names

See:

• How to clone a RECORD in PostgreSQL
• How to set value of composite variable field using dynamic SQL