CODEWITHSUNDEEP
postgresqlpostgresql-9.1sql-function
artaxerxe
artaxerxe

Reputation: 6421

Using extract function in your own sql function

I'm trying to create the following function into my Postgres database:

create or replace function public.extract_hour(time_param timestamp with time zone) returns double precision language sql as $function$
SELECT EXTRACT(hour from timestamp time_param);
$function$;

but I get the following error:

ERROR:  syntax error at or near "time_param"

I tried to put instead of time_param $0, but the same error occur. Can somebody explain me how to solve that?

Upvotes: 0

Views: 330

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658672

Obviously, you are using an older version of PostgreSQL (which you neglected to provide). Referring to parameter names is possible since PostgreSQL 9.2. In older versions you can only refer to positional parameters:

CREATE OR REPLACE FUNCTION public.extract_hour(time_param timestamptz)
  RETURNS double precision LANGUAGE sql AS
$function$
SELECT EXTRACT(hour from $1);
$function$;

Also I removed the misplaced keyword timestamp.

While referring to parameter names has been possible in PL/pgSQL functions for a long time now.
We fixed a documentation glitch to the opposite just recently.

Upvotes: 1

Related Questions