Using extract function in your own sql function

Question

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?

Erwin Brandstetter · Accepted Answer · 2013-05-15 12:35:51Z

1

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.

edited May 15, 2013 at 12:35

answered May 15, 2013 at 12:29

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using extract function in your own sql function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related