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;

POSTGRESQL - How to Create Function? - How to Declare Local Variable sqldeclare

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);

POSTGRESQL - How to Create Function? - How to Declare Local Variable sqldeclare1

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)

Previous Post
SQL SERVER – Puzzle – Working with functions to Concatenate columns
Next Post
Interview Question of the Week #028 – T-SQL Script to Detect SQL Server Version and Property

Related Posts

No results found.

1 Comment. Leave new

  • Puedes hacer algo similar a esto mediante dos opciones (Yo uso Postgresql 9.4) :
    1) Usando PGSCRIPT que corre solamente desde el lado del cliente (PgAdmin) :
    SET @INT = 1;
    SET @DATOS = SELECT 1;
    PRINT @DATOS;

    2) Usando un bloque PLSQL anónimo :
    DO $$
    DECLARE
    number INT = 1;
    BEGIN
    RAISE NOTICE ‘YOU NUMBER IS : %’, number;
    END;
    $$

    Son alternativas a evitar construir funciones, obviamente el caso (1) es mas simple y parecido a SQL, pero es un avance :-) Suerte!

    Reply

Leave a Reply