Search code examples
postgresqlsql-insertsql-function

Function to insert data into different tables

I have three tables in PostgreSQL:

CREATE TABLE organization (id int, name text, parent_id int);

CREATE TABLE staff (id int, name text, family text, organization_id int); 

CREATE TABLE clock(id int, staff_id int, Date date, Time time);

I need a function that gets all the fields of these tables as inputs (8 on total) and then inserts these inputs into appropriate fields of the tables

Here is my code:

CREATE FUNCTION insert_into_tables(org_name character varying(50), org_PID int, person_name character varying(50),_family character varying(50), org_id int, staff_id int,_date date, _time time without time zone) 
RETURNS void AS $$
BEGIN

INSERT INTO "Org".organisation("Name", "PID")
VALUES ($1, $2);
INSERT INTO "Org".staff("Name", "Family", "Organization_id")
VALUES ($3, $4, $5);
INSERT INTO "Org"."Clock"("Staff_Id", "Date", "Time")
VALUES ($6, $7, $8);

END;
$$ LANGUAGE plpgsql;

select * from insert_into_tables('SASAD',9,'mamad','Imani',2,2,1397-10-22,'08:26:47')

But no data is inserted. I get the error:

ERROR: function insert_into_tables(unknown, integer, unknown, unknown, integer, integer, integer, unknown) does not exist

LINE 17: select * from insert_into_tables('SASAD',9,'mamad','Imani',2... ^

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Where did i go wrong?

Solution

  • That's because the 2nd last parameter is declared as date, not int. You forgot the single quotes:

    select * from insert_into_tables('SASAD',9,'mamad','Imani',2,2,'1397-10-22','08:26:47');

    Without single quotes, this is interpreted as subtraction between 3 integer constants, resulting in an integer: 1397-10-22 = 1365.

    Also fix your identifiers: double-quoting preserves upper-case letters, so "Name" is distinct from name etc. See: