CODEWITHSUNDEEP
postgresqlcomposite-types
ThatDataGuy
ThatDataGuy

Reputation: 2109

How can I pass table data into a function using a PostgresSQL composite type?

I am tying to pass data into a function using a composite type. However, I can't see how to call the function. Consider the following code:

drop table if exists ids cascade;
create table ids
(
    id bigint primary key
);

drop table if exists data;
create table data
(
    id   bigint generated always as identity primary key references ids(id) deferrable initially deferred,
    name text
);

drop type if exists raw_type cascade;
create type raw_type as (name text);
create table raw2 of raw_type;

insert into raw2(name)
values ('test1'),
       ('test2'),
       ('test3'),
       ('test4'),
       ('test5'),
       ('test6'),
       ('test7')
;

create or replace function special_insert(data_to_insert raw_type) returns void as
    $func$
    begin
    with x as (insert into data(name) select name from data_to_insert returning id)
    insert into ids(id)
    select id from x;
    end;
$func$ language plpgsql;

Running this:

begin transaction ;
select special_insert(raw2);
commit ;

I get the following error:

ERROR: column "raw2" does not exist

Running this:

begin transaction ;
select special_insert(name::raw_type) from raw2;
commit ;

I get

[2019-04-10 15:41:18] [22P02] ERROR: malformed record literal: "test1"
[2019-04-10 15:41:18] Detail: Missing left parenthesis.

What am I doing wrong?

Upvotes: 0

Views: 45

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247625

The second function call is correct, however there is a bug in your function definition.

You make the mistake of treating data_to_insert as a table, but it is a single value. Use the following notation to get an individual field from a composite type:

INSERT INTO data (name)
VALUES (data_to_insert.name)
RETURNING id

Upvotes: 1

Related Questions