CODEWITHSUNDEEP
postgresqlpostgresql-9.3
MAK
MAK

Reputation: 7260

PostgreSQL 9.3: Setting parameters values default to null

I have the following funtion which contains 6 parameters.

Here I want to set the default value null to the parameters cola,colc and colf.

Function: fun_Test

create or replace function fun_Test
(
    cola int default null,
    colb varchar(10),
    colc int default null,
    cold varchar(10),
    cole varchar(10),
    colf varchar(50) default null
)

returns void AS

$$
begin
    raise info '%',cola;
    raise info '%',colb;
    raise info '%',colc;
    raise info '%',cold;
    raise info '%',cole;
    raise info '%',colf;
end;
$$
language plpgsql;

But getting an error.

ERROR: input parameters after one with a default value must also have defaults

Upvotes: 2

Views: 6199

Answers (2)

Vivek S.
Vivek S.

Reputation: 21905

This should work:

CREATE OR replace FUNCTION 
fun_Test (
     colb VARCHAR(10)
    ,cold VARCHAR(10)
    ,cole VARCHAR(10)
    ,colf VARCHAR(50) = NULL
    ,colc INT = NULL
    ,cola INT = NULL
    )

As per CREATE FUNCTION

default_expr

An expression to be used as default value if the parameter is not specified. The expression has to be coercible to the argument type of the parameter. Only input (including INOUT) parameters can have a default value. All input parameters following a parameter with a default value must have default values as well.

Upvotes: 1

Daniel
Daniel

Reputation: 28074

You have to reorder your parameters! Just place the ones with defaults at the end.

Upvotes: 5

Related Questions