issue with create function in PostgreSQL-CodePudding

I am trying to get year from orderdate function

type orderdate date

create or replace function getyearfromdate(year date)returns 
table 
as
$$
begin
    return QUERY execute (
         'select extract (year from orderdate) FROM public.orderalbum'
    );
end;
$$
language plpgsql;

I write a logic but not able to create a function

I want to return year from the orderdate.

I want to pass a orderdate and return year from the function

I am facing below error

ERROR:  syntax error at or near "as"
LINE 3: as
        ^
SQL state: 42601
Character: 70

CodePudding user response：

Based on your comments, it seems you only want a wrapper around the extract() function. In that case you do not want a set returning function. And you don't need PL/pgSQL or even dynamic SQL for this:

create or replace function getyearfromdate(p_date_value date)
  returns int --<< make this a scalar function!
as
$$
  select extract(year from p_date_value)::int;
$$
language sql;

Note that I renamed your parameter as I find a parameter named year for a date value highly confusing.

That function can then be used as part of a SELECT list:

SELECT ..., getyearfromdate(orderdate)
FROM public.orderalbum
GROUP BY ...

Original answer based on the question before comments clarified it.

As documented in the manual returns table requires a table definition.

Your use of dynamic SQL is also useless.

create or replace function getyearfromdate(year date)
  returns table (year_of_month int)
as
$$
begin
    return QUERY 
      select extract(year from orderdate)::int 
      FROM public.orderalbum;
end;
$$
language plpgsql;

I am not sure why you are passing a parameter to the function that you never use.