Postgresql – use function arguments or variable in postgres function

alter-tablefunctionspostgresql

I want to set the default value for the column building and floor in my table points in the below function called test(b,f)

Create or replace function test(b text, f text) returns void as
$$
Begin
Alter table points alter COLUMN building set default b;
Alter table points alter COLUMN floor set default f;
End;
$$
language plpgsql;

When I run select test('Shopping Mall','03') it gives me below error:

 column b does not exist

It seems that I cannot use a function arguments b in the alter ... query?

Best Answer

I have found the solution by using function triggers. I didn't dynamically change the default value but I used trigger function to update the column value before insert action. It can achieve the same result.

Create or replace function update_value() 
Returns Trigger AS $$
BEGIN
IF NEW.floor ISNULL THEN
    NEW.floor :=(select floor from buildings where my_current_location='yes');
END IF;

IF NEW.building ISNULL THEN                                         
    NEW.building :=(select name from buildings my_current_location='yes');      
END IF;
RETURN NEW;
END $$ Language 'plpgsql';

Also you need to add a trigger to the table:

Create triggers update_value_trigger Before Insert On points For each row execute procedure update_value();

Related Solutions

Postgresql – Get TEXT value of a CLOB OID in Postgresql

Thanks goes to a_horse_with_no_name. Solution is:

update answers set answer_value = lo_get(cast(value as bigint))

Note - the lo_get function appears to be present in Postgres 9.4 or higher. For earlier versions, I don't see a way of doing this directly. I'm currently running 9.0, but this just accelerated my upgrade plans.

Function Variable Number of Arguments plsql

DECODE is an SQL function, not PL/SQL. You can not use it in plain PL/SQL, only as part of SQL statements.

There is no nice and easy way for this. There is the method with collections, for example:

create or replace type t_numbers as table of number;
/

create or replace function sum_plsql (p_numbers t_numbers) return number
as
  rv number := 0;
begin
  for n in 1..p_numbers.count
  loop
    rv := rv + p_numbers(n);
  end loop;
  return rv;
end;
/

Then:

select sum_plsql(t_numbers(1, 1, 1, 1, 1, 1, 1, 1, 1)) from dual;

9

There are built-in, non-documented simple list types, so you do not need to define your own types at all circumstances, but relying on non-documented features is generally bad practice, as they may change in newer versions without warning.

If there is a reasonable limit for the number of arguments:

create or replace function sum_plsql2 (p1 number default 0, p2 number default 0, p3 number default 0, p4 number default 0) return number
as
begin
  return p1 + p2 + p3 + p4;
end;
/

select sum_plsql2(1, 1, 1) from dual;

3

Obviously, these are neither tidy nor effective.

Best Answer

Related Solutions

Postgresql – Get TEXT value of a CLOB OID in Postgresql

Function Variable Number of Arguments plsql

Related Question