Composite type within a composite type?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Composite type within a composite type?

Juan Miguel Paredes
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!!

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: Composite type within a composite type?

Tom Lane-2
Juan Miguel Paredes <[hidden email]> writes:
> DECLARE
>   res helpdesk.tp_res_conestadosdisponiblessoporte;
> BEGIN
>   IF estadoactual = 'Abierto' THEN
>     SELECT INTO res.filas *
>     FROM tbl_estadosoporte

Without having looked at the code, I suspect that plpgsql just assumes
res.filas is of scalar type and so fails to match it up to the entire
SELECT INTO * list.  (It's not obvious how to do "better" for arbitrary
combinations of scalar types, composite types, and user expectations...)

I'd recommend that you declare a row variable of type tbl_estadosoporte,
select into that, and then worry about constructing the larger composite
result.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match