In Postgres sql Insert to table using user defined datatype-CodePudding

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

CodePudding user response：

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;
$$;