0

I want to create a function in Posgresql which returns a table:

create or replace function my_func1(var1 integer, var2 integer[], var3 integer)
  returns table(col1 integer, col2 integer, col3 timestamp) 
as

begin

    select  .........
end;
language sql;

It says:

ERROR:  syntax error at or near "begin"
Torito
  • 305
  • 1
  • 11

2 Answers2

2

There is no begin in an SQL function. Did you mean to create a plpgsql function?

Then use LANGUAGE plpgsql instead.
And enclose the function body in quotes either way, it's text - preferably dollar quotes:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • where should I place "$func$" in the end of a function: before or after `language plpgsql;`? – Torito Feb 06 '17 at 06:21
  • 1
    @Torito: please see the examples in the manual. [here](https://www.postgresql.org/docs/current/static/sql-createfunction.html#SQL-CREATEFUNCTION-EXAMPLES) and [here](https://www.postgresql.org/docs/current/static/xfunc-sql.html) and [here](https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#AEN65553) –  Feb 06 '17 at 09:00
  • @a_horse_with_no_name, I don't need to. – Torito Feb 06 '17 at 10:40
  • @Torito: then why did you ask "where should I place.."? That is clearly answered in the manual. –  Feb 06 '17 at 10:49
  • @a_horse_with_no_name, no. – Torito Feb 06 '17 at 11:45
  • 2
    @Torito: it **is** shown in the links I gave you. But if you don't want to read the manual, at least read the linked answer from Erwin. He explained in great length what that is and how to use it –  Feb 06 '17 at 12:33
  • @a_horse_with_no_name, no, he didn't. – Torito Feb 06 '17 at 14:58
  • Database expert, how about my question? – Torito Feb 06 '17 at 14:59
  • @Torito: You can place `LANGUAGE plpgsql` before or after the function body, makes no difference. But not *inside*, obviously. – Erwin Brandstetter Feb 06 '17 at 15:15
1

Try this:

create or replace function my_func1(var1 integer, var2 integer[], var3 integer)
returns table(col1 integer, col2 integer, col3 timestamp) 
as
$func$
begin
    return query 
    select  .........
end;
$func$
language plpgsql;
Ben H
  • 435
  • 5
  • 15