1

I created simple table with a simple function, to insert some logs for the elapsed semester:

CREATE TABLE log_elapsedsemester(
sy char(9) NOT NULL,
sem char(1) NOT NULL,
date_recorded TIMESTAMP NOT NULL,
recordedby varchar(255)
);

CREATE OR REPLACE FUNCTION addelapsedsemester(p_sy char,p_sem char,p_date_recorded 
TIMESTAMP,p_recordedby varchar)
returns void
AS
$$
BEGIN
insert into log_elapsedsemester (sy,sem,date_recorded,recordedby) values 
(p_sy,p_sem,p_date_recorded,p_recordedby);
END
$$
LANGUAGE plpgsql;

But evertime I use

select addelapsedsemester('2019-2020','1',now(),'sample@gmail.com');

I get the error:

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

If I use a simple INSERT with no function it inserts successfully:

insert into log_elapsedsemester(sy,sem,date_recorded,recordedby) values ('2020- 
2021','1',now(),'sample@gmail.com');

I'm using PostgreSQL 9.5 with pgadmin III.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Jack Clarkson
  • 95
  • 1
  • 7

1 Answers1

1

You need to cast to timestamp explicitly. Like:

SELECT addelapsedsemester('2019-2020', '1', now()::timestamp,'sample@gmail.com');

Or use LOCALTIMESTAMP instead of now()::timestamp (equivalent).

The function now() returns type timestamp with time zone (timestamptz), while your function takes timestamp without time zone (timestamp). The now() function produces a typed value (unlike the other untyped literals), where Postgres is more hesitant to coerce it to a different type. Function type resolution does not succeed.

The same type coercion still works for the bare INSERT command because (quoting the manual):

If the expression for any column is not of the correct data type, automatic type conversion will be attempted.

Be aware that the cast from timestamptz to timestamp depends on the current timezone setting of the session. You may want to be more explicit. Like now() AT TIME ZONE 'Europe/London'. Or use timestamptz to begin with. Then your original call without cast just works. See:

Also, you most probably do not want to use the type char, which is misleading short syntax for character(1). Use text or varchar instead. See:

This table definition would make more sense:

CREATE TABLE log_elapsedsemester(
  sy varchar(9) NOT NULL
, sem integer   NOT NULL
, date_recorded timestamptz NOT NULL
, recordedby text
);

Or even:

sy integer NOT NULL  -- 2019 can stand for '2019-2020'

Function parameters would match the column type.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228