POSTGRESQL – How to Create Function? – How to Declare Local Variable

I have written few courses on PostgreSQL on Pluralsight. You can watch list of all the courses over here.

Recently, after watching my courses one of the user who is familiar with SQL Server but just beginning with PostgreSQL asked me very interesting questions. Let us here the question in his own words.

“How to declare local variables in PostgreSQL? It is very easy to declare local variables in SQL Server but it seems not possible in PostgreSQL. Any suggestions?”

I loved this question as in PostgreSQL, there is no notion of much procedural language. In the recent version, it is getting better with some of the support, but overall, PostgreSQL is not easy to deal with when there are set of codes and one has to execute them in succession.

Let us see a simple example of SQL Server code:

DECLARE @Int1 INT = 1
SELECT @Int1 Col1;

Now let us see the same example in PostgreSQL.

CREATE OR REPLACE FUNCTION DisplayFunction(
IN Int1 integer,
OUT p_film_count integer)
RETURNS SETOF integer AS
$BODY$
SELECT $1;
$BODY$
LANGUAGE sql;
SELECT DisplayFunction(1);

You can clearly see how PostgreSQL requires the user to do more work to accomplish simple task. I like PostgreSQL very much and I hope that it comes out with more support to procedural languages.

Reference: Pinal Dave (https://blog.sqlauthority.com)