2

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Reza
  • 113
  • 2
  • 11

1 Answers1

1

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • i put it in single quotes but now the error comes: ERROR: relation "Org.organisation" does not exist LINE 1: INSERT INTO "Org".organisation( ^ QUERY: INSERT INTO "Org".organisation( "Name", "PID") VALUES ($1, $2) CONTEXT: PL/pgSQL function insert_into_tables(character varying,integer,character varying,character varying,integer,integer,date,time without time zone) line 3 at SQL statement SQL state: 42P01 – Reza Jan 12 '19 at 08:13
  • @Reza: Did you read the part about quoted identifiers in my answer The [`search_path`](https://stackoverflow.com/a/9067777/939860) may also play a role. My crystal ball is shrouded in mist. – Erwin Brandstetter Jan 12 '19 at 08:52
  • see, when i write each insert individually, for ex INSERT INTO "Org".organisation( "Name", "PID") VALUES ('OS', 13); it works fine. but here it throws error. what is the problem? – Reza Jan 12 '19 at 10:09
  • 1
    Error with quoted identifiers (did you use the exact same spelling?), connected with a different role (missing privileges on the schema `"Org"` or to a different db by mistake - test with `SELECT current_database();` and makes sure you connected to the same cluster (host, port). – Erwin Brandstetter Jan 12 '19 at 14:51
  • yep... the problem was that on the top of the query in which i was writing commands, it was written db1 on postgres@POSTGRESQL 10, that is not the appropriate database. it should have been written Payment on postgres@POSTGRESQL 10. i don't know why it was changed!!! Thanks Erwin! – Reza Jan 13 '19 at 05:52