How to create a function in PostgreSQL?

Question

I`ve got only one database which name is "testDB". In command line: 1)

psql testDB

2) create a function as easies as is possible:

CREATE or replace FUNCTION testFun()
  RETURNS integer AS $total$
declare
  total integer;
BEGIN
  SELECT count(*) into total FROM "Run";
  RETURN total;
END;
 $$ LANGUAGE plpgsql;

I don`t know what it is for?: $$ LANGUAGE plpgsql; but it was in some example.

So I copied it to command line.

After all nothing happen.

user@LenovoUbuntu:~$ psql testDB 
psql (9.1.11)
Type "help" for help.

testDB=> CREATE or replace FUNCTION testFun()
testDB->   RETURNS integer AS $total$
testDB$> declare
testDB$>   total integer;
testDB$> BEGIN
testDB$>   SELECT count(*) into total FROM "Run";
testDB$>   RETURN total;
testDB$> END;
testDB$>  $$ LANGUAGE plpgsql;
testDB$>

Pay attention on:

testDB=>
testDB->
testDB>

score 2 · Accepted Answer · answered Feb 23 '14 at 11:05

2

Just modify the last line from:

 $$ LANGUAGE plpgsql;

To

 $total$ LANGUAGE plpgsql;

You can call your function by

Select testFunc();

Create function documentation

answered Feb 23 '14 at 11:05

Houari

5,326
3
31
54

OK, thank you, but what is it? $total$ LANGUAGE plpgsql; What for? – andrew Feb 23 '14 at 11:10
1

Copying examples is not enough - you need to read the [fine](http://www.postgresql.org/docs/current/static/sql-createfunction.html) [documentation](http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING). – Milen A. Radev Feb 23 '14 at 11:15
1

@andrew Here is why: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING – Houari Feb 23 '14 at 11:27

How to create a function in PostgreSQL?

1 Answers1