pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdate a composite nested type variable
4+ messages / 2 participants
[nested] [flat]
* Update a composite nested type variable
@ 2013-07-08 13:11 Luca Vernini <lucazeo@gmail.com>
2013-07-08 19:01 ` Re: Update a composite nested type variable David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Luca Vernini @ 2013-07-08 13:11 UTC (permalink / raw)
To: pgsql-sql
I'm writing a system with havy use of composite types.
I have a doubt.
I'm writing all in functions with language plpgsql.
When I read a field from a composite type I must write something like this:
status = ((in_customer.customer_data).customer_status).status_id
And this works fine. I need to enclose the base type, but this is not a
problem.
When I need to assign a value I try to write something like:
(in_customer.customer_data).field_a := NULL;
But postgresql rise an error:
ERROR: syntax error at or near "("
SQL state: 42601
If I dont use parentesis I rise a different error:
ERROR: "in_customer.customer_data.field_a" is not a known variable
SQL state: 42601
2 questions:
Why is the behavior so different in read and in assign.
How can I workaround this and update my values?
Luca.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Update a composite nested type variable
2013-07-08 13:11 Update a composite nested type variable Luca Vernini <lucazeo@gmail.com>
@ 2013-07-08 19:01 ` David Johnston <polobo@yahoo.com>
2013-07-08 19:44 ` Re: Update a composite nested type variable Luca Vernini <lucazeo@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: David Johnston @ 2013-07-08 19:01 UTC (permalink / raw)
To: pgsql-sql
Luca Vernini wrote
> I'm writing a system with havy use of composite types.
> I have a doubt.
>
> I'm writing all in functions with language plpgsql.
> When I read a field from a composite type I must write something like
> this:
> status = ((in_customer.customer_data).customer_status).status_id
>
> And this works fine. I need to enclose the base type, but this is not a
> problem.
>
> When I need to assign a value I try to write something like:
> (in_customer.customer_data).field_a := NULL;
>
> But postgresql rise an error:
> ERROR: syntax error at or near "("
> SQL state: 42601
>
> If I dont use parentesis I rise a different error:
>
> ERROR: "in_customer.customer_data.field_a" is not a known variable
> SQL state: 42601
>
> 2 questions:
> Why is the behavior so different in read and in assign.
> How can I workaround this and update my values?
>
> Luca.
This may be a pl/pgsql limitation but you should probably provide a complete
self-contained example with your attempt so that user-error can be
eliminated.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Update-a-composite-nested-type-variable-tp5763023p5763082.ht...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Update a composite nested type variable
2013-07-08 13:11 Update a composite nested type variable Luca Vernini <lucazeo@gmail.com>
2013-07-08 19:01 ` Re: Update a composite nested type variable David Johnston <polobo@yahoo.com>
@ 2013-07-08 19:44 ` Luca Vernini <lucazeo@gmail.com>
2013-07-08 23:25 ` Re: Update a composite nested type variable David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Luca Vernini @ 2013-07-08 19:44 UTC (permalink / raw)
To: pgsql-sql
2013/7/8 David Johnston <polobo@yahoo.com>
>
> This may be a pl/pgsql limitation but you should probably provide a
> complete
> self-contained example with your attempt so that user-error can be
> eliminated.
>
> David J.
>
>
All right. Here you are a complete example. Just tested it.
Sorry for the long email.
CREATE TYPE type_customer AS
(id integer,
cust_name character varying(100),
email character varying(100));
CREATE TYPE type_supercustomer AS
(cus_data type_customer,
superpower character varying);
CREATE TABLE public.table_customer
(
id serial NOT NULL,
cust_name character varying(100) NOT NULL,
email character varying(100) NOT NULL,
PRIMARY KEY (id)
)
WITH (OIDS = FALSE);
CREATE TABLE public.table_supercustomer
(
superpower character varying(100) NOT NULL
)
INHERITS (table_customer)
WITH (OIDS = FALSE);
ALTER TABLE table_supercustomer ADD PRIMARY KEY (id);
CREATE OR REPLACE FUNCTION function_read_supercustomer()
RETURNS SETOF type_supercustomer AS
$BODY$
DECLARE
retset type_supercustomer;
BEGIN
FOR retset IN
SELECT (id, cust_name, email), superpower
FROM table_supercustomer
LOOP
retset.superpower := initcap(retset.superpower);--works
(retset.cus_data).email = 'anything you want';--does not work
RETURN NEXT retset;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql STABLE STRICT COST 100 ROWS 1000;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Update a composite nested type variable
2013-07-08 13:11 Update a composite nested type variable Luca Vernini <lucazeo@gmail.com>
2013-07-08 19:01 ` Re: Update a composite nested type variable David Johnston <polobo@yahoo.com>
2013-07-08 19:44 ` Re: Update a composite nested type variable Luca Vernini <lucazeo@gmail.com>
@ 2013-07-08 23:25 ` David Johnston <polobo@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: David Johnston @ 2013-07-08 23:25 UTC (permalink / raw)
To: pgsql-sql
Luca Vernini wrote
> 2013/7/8 David Johnston <
> polobo@
> >
>
>>
>> This may be a pl/pgsql limitation but you should probably provide a
>> complete
>> self-contained example with your attempt so that user-error can be
>> eliminated.
>>
>> David J.
>>
>>
> All right. Here you are a complete example. Just tested it.
> Sorry for the long email.
This does appear to be a limitation. The documentation says pl/pgsql allows
for "simple variables" in the target which 2-layer composite types do not
qualify for.
As a work-around I'd suggest creating local variables for each of the
relevant fields - say by using the same names but with "_" instead of ".";
You will then need to reconstruct each complex value from the basic values
and return the reconstructed value.
r_cus_id := retset.cus_data.id;
r_cus_name := retset.cus_data.name;
r_cus_email := retset.cus_data.email;
r_superpower := retset.superpower:
RETURN SELECT (r_cus_id, r_cus_name, r_cus_email)::type_customer,
r_superpower)::type_supercustomer;
Not tested but as I am writing this I am getting a Deja-Vu sensation which I
think means I am correct and that this somewhat convoluted way is what
you've got.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Update-a-composite-nested-type-variable-tp5763023p5763119.ht...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2013-07-08 23:25 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-07-08 13:11 Update a composite nested type variable Luca Vernini <lucazeo@gmail.com>
2013-07-08 19:01 ` David Johnston <polobo@yahoo.com>
2013-07-08 19:44 ` Luca Vernini <lucazeo@gmail.com>
2013-07-08 23:25 ` David Johnston <polobo@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox