Plpgsql: How can i assign value to variable at declaration section?

Question

For example.

--Assigning value to variable in function as a parameter.
create or replace function f1(number int :=1) --This method is not working to me.

or

--Assigning values to variables at declaration section.
declare
       number int :=1; -- Here i need to assign the value to number but its not working.
       name varchar :='xyz';

First, i advice you to not use the postgreSQL's tokens as name of variables. Second, why it does not work, could you please post error message, more about your function, your PostgreSQL version ? — Houari, Feb 21 '14 at 11:10
--My function create or replace function "funCreateAnalysisView"("AnalysisMID" bigint :=1) --Error Message ERROR: syntax error at or near ":=" LINE 1: ...e function "funCreateAnalysisView"("AnalysisMID" bigint :=1) ^ — Sarfaraz Mak, Feb 21 '14 at 11:17
@SarfarazMak Edit your question to add more info, don't just add comments. Otherwise it's very hard to read. Edit, then add a comment when you've made the requested edits. — Craig Ringer, Feb 21 '14 at 11:18

score 2 · Accepted Answer · answered Feb 21 '14 at 11:19

2

Here is how you can do it:

create or replace function f1(my_number int default 1)

or

declare
my_number int :=1;

Look at declaration documentation

answered Feb 21 '14 at 11:19

Houari

5,326
3
31
54

Working fine. Thank you Houari. And i will also take care of making my question more clear next time. Sorry for this time. – Sarfaraz Mak Feb 21 '14 at 11:22

Erwin Brandstetter · Answer 2 · 2018-04-13T01:40:18.940

There is more to it.

create or replace function f1(number int :=1) --This method is not working to me.

This works:

CREATE OR REPLACE FUNCTION f1(number int = 1) ...

Because := is the assignment operator of PL/pgSQL, while it's = for SQL. Details under this related question:

The forgotten assignment operator "=" and the commonplace ":="

And CREATE FUNCTION is an SQL statement, even when creating a plpgsql function.

Inside plpgsql both variants are accepted, but only := is correct. = is tolerated, since it's such a widespread mistake people make. (Update: since pg 9.4, both variants are documented.) However, there are a few cases where the distinction is mandatory. For instance when calling the above function with named parameters. It has to be:

SELECT * FROM f1(number := 1);

Better yet, in Postgres 9.5 or later use => for parameter assignment in a function call:

SELECT * FROM f1(number => 1);

With:

SELECT * FROM f1(number = 1);

... Postgres would interpret number = 1 as an SQL expression and try to evaluate it, first looking for the identifier number in the outer scope of the calling statement. If it can't be found you get:

ERROR:  column "number" does not exist

That's the lucky case, and also the more common one. If number can be found in the scope of the calling statement and the evaluated boolean expression can be accepted as function parameter, you have successfully built an evil trap. Hard to debug if you were not aware of the distinction between := and = in the first place.

Is "= is tolerated" still the case as of 2018? Or "=" is totally the same as ":=" now? — LiweiZ, Apr 13 '18 at 01:14
@LiweiZ: Since Postgres 9.4, both are documented (= totally the same). — Erwin Brandstetter, Apr 13 '18 at 01:31

score 1 · Answer 3 · edited Jun 20 '20 at 09:12

Assign value, general note

(about assign value to variable out of declaration section)

The language PLpgSQL syntax have many ways to say:

 Y := f(X);

The EXECUTE clause is only for "dynamic execution" (less performance),

 EXECUTE 'f(X)' INTO Y;

Use Y := f(X); or SELECT for execute static declarations,

 SELECT f(X) INTO Y;

Use PERFORM statment when discard the results or to work with void returns:

 PERFORM f(X);

Plpgsql: How can i assign value to variable at declaration section?

3 Answers3

Assign value, general note

Linked