Postgres Pro
Downloads
Home   >   mailing lists

Thread: Using composite types within PLPGSQL Function

Using composite types within PLPGSQL Function

From
Graeme Hinchliffe
Date:

Hi,

                I am trying to use a composite type within a function, I have tried SELECT INTO and direct assignment to set the value of a single element within the composite variable with no joy.  I hope this is simply a case of my not knowing the correct syntax?

 

My example code is :

 

CREATE TYPE testtype AS (

a INTEGER,

b INTEGER

);

 

CREATE OR REPLACE FUNCTION test() RETURNS INTEGER AS $$

DECLARE

  x testtype;

BEGIN

(x).a:=1;

RETURN 1;

END

$$ LANGUAGE plpgsql;

 

This throws up syntax errors for the (x).a:=1; line.. I have also tried SELECT INTO (x).a 1;

 

Any help much appreciated, version of PostgreSQL is 8.4 under Debian.

 

Thanks

 

Graeme

--
Graeme Hinchliffe
Senior Systems Designer, Zen Internet
T: 0845 058 9000
F: 0845 058 9005
W: zen.co.uk

BT have increased their monthly phone line rental to £15.45 per month. Our remains at £11.22. Switch now : http://www.zen.co.uk/home-office/voice/phone-services.aspx

This message is private and confidential. If you have received this message in error, please notify us and remove it from your system.

Zen Internet Limited may monitor email traffic data to manage billing, to handle customer enquiries and for the prevention and detection of fraud. We may also monitor the content of emails sent to and/or from Zen Internet Limited for the purposes of security, staff training and to monitor quality of service.

Zen Internet Limited is registered in England and Wales, Sandbrook Park, Sandbrook Way, Rochdale, OL11 1RY Company No. 03101568 VAT Reg No. 686 0495 01

Re: Using composite types within PLPGSQL Function

From
Leif Biberg Kristensen
Date:
 Tirsdag 8. januar 2013 16.10.03 skrev Graeme Hinchliffe :
> My example code is :
>
> CREATE TYPE testtype AS (
> a INTEGER,
> b INTEGER
> );
>
> CREATE OR REPLACE FUNCTION test() RETURNS INTEGER AS $$
> DECLARE
>   x testtype;
> BEGIN
> (x).a:=1;
> RETURN 1;
> END
> $$ LANGUAGE plpgsql;
>
> This throws up syntax errors for the (x).a:=1; line.. I have also tried
> SELECT INTO (x).a 1;
>
> Any help much appreciated, version of PostgreSQL is 8.4 under Debian.

It should work with

x.a := 1;

without the parentheses. See http://solumslekt.org/blog/?p=91 for an example
of composite types and functions.

regards, Leif