From: | Juan Miguel Paredes <juan(dot)paredes(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Composite type within a composite type? |
Date: | 2005-07-18 21:03:53 |
Message-ID: | 9e0a286905071814031e9140aa@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi, folks!
Reading previous posts on returning composite types in pl/pgsql, I
still haven't found a good answer for this issue:
Let's say we create this table:
/*************************************************************************************/
CREATE TABLE "tbl_estadosoporte" (
"id" CHAR(1) NOT NULL,
"nombreestado" VARCHAR(20) NOT NULL,
CONSTRAINT "tbl_estadosoporte_pkey" PRIMARY KEY("id")
) WITH OIDS;
/*************************************************************************************/
Now, I want a pl/pgsql function returning:
a) error_code (depending on logic conditions)
b) Result set (tbl_estadosoporte%TYPE)
My first guess was:
/*************************************************************************************/
CREATE TYPE "tp_res_conestadosdisponiblessoporte" AS (
"codigoerror" VARCHAR(100),
"filas" tbl_estadosoporte /* Implicit Composite type created with table */
);
/*************************************************************************************/
But, when executing pl/pgsql function:
/*************************************************************************************/
CREATE OR REPLACE FUNCTION "conestadosdisponiblessoporte"
(estadoactual varchar) RETURNS "tp_res_conestadosdisponiblessoporte"
AS
$body$
DECLARE
res helpdesk.tp_res_conestadosdisponiblessoporte;
BEGIN
IF estadoactual = 'Abierto' THEN
SELECT INTO res.filas *
FROM tbl_estadosoporte
WHERE id NOT IN ('A','P')
ORDER BY id;
/* SOME OTHER CONDITIONS HERE... */
END IF;
res.codigoerror = 'OK'
RETURN res;
EXCEPTION
/* SOME ERROR TRAPPING */
WHEN OTHERS THEN
res.codigoerror = 'Generic error :p';
RETURN res;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
/*************************************************************************************/
(BTW, this is just a work in progress.. :D )
I got the error:
ERROR: cannot assign non-composite value to a row variable.
Couldn't find other posts regarding the above message...
Perhaps it's just a design problem... this was a T-SQL (MsSQLServer)
stored procedure, with both OUTPUT parameters and a resultset...
Any help or suggestions greatly appreciated.
Thx!!
From | Date | Subject | |
---|---|---|---|
Next Message | Dan Armbrust | 2005-07-18 21:09:57 | Re: index row size exceeds btree maximum, 2713 - Solutions? |
Previous Message | Dan Armbrust | 2005-07-18 21:01:06 | Re: index row size exceeds btree maximum, 2713 - Solutions? |