Re: Couldn't cast to record[]

Hi

čt 3. 3. 2022 v 11:01 odesílatel Suresh Kumar R <suresh(dot)arsenal29(at)gmail(dot)com>
napsal:

> Hi, I created an table with composite type array as datatype for one
> column.
> When I query that table I need the pg_typeof(column) as record[] instead of
> composite_type[].
> I tried creating a separate function and returning record[], but below
> error
> is thrown,
>
> ERROR: PL/pgSQL functions cannot return type record[]
>
> Below is sample for my requirement.
>
> pali=# create type address as (number bigint, city varchar);
> CREATE TYPE
> pali=# create table person(name varchar, addresses address[]);
> CREATE TABLE
> pali=# insert into person values ('Michael', array[(82, 'NYC'),(24,
> 'Dunkirk')]::address[]);
> INSERT 0 1
> pali=# select pg_typeof(addresses::record[]) from person ;
> pg_typeof
> -----------
> address[]
>
> Here I expect record[] instead of address[].
>
> Thanks in advance.
>

record type is not real PostgreSQL's type. It is just PLpgSQL only type.
You cannot use it outside PLpgSQL.

Regards

Pavel