Forum OpenACS Development: I could not able to execute nvl function from PostgreSql

Collapse
1: I could not able to execute nvl function from PostgreSql
Posted by VenuMadhav Deevi on 05/05/03 10:14 AM
Hi,
  I could not able to execute the "nvl function" from PostgreSql database. why i am writing this is i want to replace the field data if it is null with another value for a select query as it is important for my package.
  Plz help me out how to do this. For this i searched the documentation on PostgreSQL site also i could not able to get any help from there.

Regards

Venu Madhav Deevi

Collapse
2: Re: I could not able to execute nvl function from PostgreSql (response to 1)
Posted by Neophytos Demetriou on 05/05/03 10:30 AM
Venu, nvl is an oracle construct. You can replicate its behavior in PostgreSQL with coalesce.
Collapse
3: Re: I could not able to execute nvl function from PostgreSql (response to 1)
Posted by Andrei Popov on 05/05/03 11:39 AM
...or you could define one in pl/pgsql itself, probably like this (but you may need to overload it for several data types): 
-- Oracle NVL for varchars
create or replace function nvl(varchar,varchar) returns varchar as '
  declare
    p_expr1 alias for $1;
    p_expr2 alias for $2;
    v_result varchar;
  begin
    -- sanity checks
    if p_expr2 is null then
       raise exception ''Value for expr2 cannot be NULL!'';
       return '''';
    else
      select coalesce (p_expr1, p_expr2)
      into v_result;
    end if;
    return v_result;
  end;
' language 'plpgsql';
Collapse
4: Re: I could not able to execute nvl function from PostgreSql (response to 1)
Posted by Don Baccus on 05/05/03 06:46 PM
Yes, you can, but since query files make it easy to write a custom query to use nvl() rather than coalesce() for Oracle, that's the preferred approach.

Also Oracle 9i supports coalesce().  So the preferred approach is to use coalesce() in a query in the generic query file for the script (assuming the query doesn't contain other PG/Oracle specific constructs), and the Oracle-only nvl() in an Oracle-specific queryfile.

Then later, when we drop support of 8i (which won't happen for a year or more, I'm sure!), one could remove the Oracle-specific version and both DBs would use the same query.

Oracle 9i supports much more of the SQL 99 standard and eventually, over time, we'll be able to share a lot more queries between PG and Oracle because of it.  That's a nice future to contemplate!