Unable to create a PG sql function

Question

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"

score 2 · Answer 1 · edited May 23 '17 at 11:53

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:

What are '$$' used for in PL/pgSQL

edited May 23 '17 at 11:53

Community

1
1

answered Feb 06 '17 at 06:00

Erwin Brandstetter

539,169
125
977
1,137

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) – a_horse_with_no_name 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. – a_horse_with_no_name 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 – a_horse_with_no_name 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

Ben H · Accepted Answer · 2017-02-06T12:18:59.747

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;

edited Feb 06 '17 at 12:18

answered Feb 06 '17 at 08:45

Ben H

415
8
15

Unable to create a PG sql function

2 Answers2