In Postgres sql Insert to table using user defined datatype

Question

The question is, in PostgreSQL how to insert to a table either using a procedure or a function where the input parameter is user defined datatype?

In the below code when we run an error ": ERROR: relation" throws. How to use direct user defined data type for inserting.

Side note: There are many examples of direct insert using UDT in stackoverflow, but the question here is specific to insert from stored procedure or function.

CREATE SCHEMA tooldb;
CREATE TYPE  tooldb.point AS
(
  firstpoint  int,
  lastpoint int
);

create table if not exists tooldb.points(
    firstpoint int,
    lastpoint int
);

CREATE OR REPLACE procedure tooldb.point_insert(
    in_point tooldb.point
)

LANGUAGE plpgsql AS  $$
BEGIN
    insert into tooldb.points (firstpoint, lastpoint)
    select firstpoint , lastpoint from in_point;
END  $$;


call tooldb.point_insert((3,5));

The procedure call is failing

saying

psql:commands.sql:24: ERROR:  relation "in_point" does not exist
LINE 2:     select firstpoint , lastpoint from in_point

insert into tooldb.points (firstpoint, lastpoint) values (in_point.firstpoint, in_point.lastpoint); — Stefanov.sm
– Stefanov.sm, Commented Nov 17, 2022 at 7:59
Thank you. That works. How do we insert multiple rows? I mean, should we use a array parameter? — Naresh
– Naresh, Commented Nov 17, 2022 at 8:05
Yes. The procedure then shall have in_point tooldb.point[] argument. I will update my answer below. — Stefanov.sm
– Stefanov.sm, Commented Nov 17, 2022 at 8:07

Stefanov.sm · Accepted Answer · 2022-11-17 08:10:06Z

2

PL/pgSQL is not needed, plain SQL is enough.

CREATE OR REPLACE procedure tooldb.point_insert(in_point tooldb.point)
language sql as
$$
 insert into tooldb.points (firstpoint, lastpoint)
 values (in_point.firstpoint, in_point.lastpoint);
$$;

For inserting multiple rows you may provide an array of tooldb.point as a procedure argument:

CREATE OR REPLACE procedure tooldb.point_insert(in_point tooldb.point[])
language sql as
$$
 insert into tooldb.points (firstpoint, lastpoint)
 select el.firstpoint, el.lastpoint from unnest(in_point) as el;
$$;

edited Nov 17, 2022 at 8:10

answered Nov 17, 2022 at 8:03

Stefanov.sm

13.3k2 gold badges25 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

In Postgres sql Insert to table using user defined datatype

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related