PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to fetch from cursor over single column of composite type into a variable of that type in PL/pgSQL?

Given:

CREATE TYPE my_struct AS (
    a int4,
    b text,
    c float4
);

DO $$
DECLARE
    my_cursor refcursor;
    my_value my_struct;
BEGIN
    OPEN my_cursor FOR SELECT (1, 'a', 10.0)::my_struct;
    FETCH my_cursor INTO my_value; -- ERROR!
END
$$

Postgres raises an error on FETCH:

ERROR: invalid input syntax for type integer: "(1,a,10)"

Which looks like it’s trying to stuff the entire composite value from the cursor into my_value.a field. I also tried

FETCH my_cursor INTO my_value.a, my_value.b, my_value.c;

But it raises the same error. What am I doing wrong and how do I do it correctly? I want to end up with my_value = (1, 'a', 10.0), or my_value IS NULL when the query yields NULL in the column.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Looks like there is simply no straightforward way to do it, but I found a somewhat palatable workaround, by naming the column and going through a temporary record.

    DO $$
DECLARE
    my_cursor refcursor;
    my_value my_struct;
    my_record record;
BEGIN
    OPEN my_cursor FOR
    SELECT (1, 'a', 10.0)::my_struct AS the_value;
                
    FETCH my_cursor INTO my_record;
    my_value := my_record.the_value;
    RAISE NOTICE '%', my_value;
END
$$

  2. 0 
    DO $$
DECLARE
    my_cursor refcursor;
    my_value my_struct;
BEGIN
    OPEN my_cursor FOR SELECT t.* FROM (VALUES(1, 'a', 10.0)) as t;
    FETCH my_cursor INTO my_value;
    RAISE NOTICE '%', my_value;
END
$$;
NOTICE:  (1,a,10)
DO

    This expands the SELECT output into a row that then matches the my_value structure.

    UPDATE

    create table my_table(id integer, struct_fld my_struct);

insert into my_table values (1, (1,a,10.0)::my_struct);
insert into my_table values (1, (1,'a',10.0)::my_struct);
insert into my_table values (2, (2,'b',20.0)::my_struct);
insert into my_table values (2, null);

DO $$
DECLARE
    my_cursor refcursor;
    my_value my_struct;
BEGIN
    OPEN my_cursor FOR SELECT (struct_fld).* from my_table;
    FETCH my_cursor INTO my_value; 
    RAISE NOTICE '%', my_value;     
    FETCH my_cursor INTO my_value;
    RAISE NOTICE '%', my_value;
    FETCH my_cursor INTO my_value;
    RAISE NOTICE '%', my_value;
END
$$;

NOTICE:  (1,a,10)
NOTICE:  (2,b,20)
NOTICE:  (,,)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search