Thread: Update a composite nested type variable
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
(in_customer.customer_data).field_a := NULL;
ERROR: syntax error at or near "("
SQL state: 42601
ERROR: "in_customer.customer_data.field_a" is not a known variable
SQL state: 42601
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.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
2013/7/8 David Johnston <polobo@yahoo.com>
All right. Here you are a complete example. Just tested it.
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;
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;
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.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.