[SQL] nvl() function

[Steven Dahlin]

I am trying to find the equivalent in Postgresql to the Oracle sql function
nvl().  With nvl() you give two parameters.  The first may be a field/column
or variable.  If the value is not null then it is returned by the function.
For example the with query below if the :ClientParameter is passed then only
those rows which have a clientdesc matching the parameter are returned.  If
the :ClientParameter is null then those rows which have clientdesc =
clientdesc are returned (all rows):

selectclientid,
 clientdesc
 from clients
 where   ( clientdesc = nvl( :ClientParameter, clientdesc ) )

I have looked thru all the documentation I could find but nowhere were any
built-in SQL functions delineated.  Does anyone know where the documentation
can be found?

Thanks,
Steve



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] error on line 1 trying to execute a script using psql

[Steven Dahlin]

I am attempting to generate a temporary function to execute some dml with
the following script:

create or replace function setuplicense() returns integer as $$
declare
  hwcustid  integer := 0;
  retvalinteger := 0;
begin
  insert into license.customer
  ( customer_id ) values ( hwcustid );
  commit;
  return retval;
end;
$$
LANGUAGE plpgsql;

select setuplicense();

When running this with psql I get the following:

Error: syntax error at or near "create"

Does anyone have a suggestion as to what is causing this?

Thanks

[SQL] running scripts like oracle sqlplus

[Steven Dahlin]

I have been trying to figure out how I can run a pgsql script like I can run
a plsql script with oracle's sqlplus.  Here is a sample script file for what
I want to run:

declare
  sysuserid integer := 0;
  hwcustid  integer := 0;
begin

select nextval( 'user_seq' ) into  sysuserid;
select nextval( 'customer_seq' ) into  hwcustid;

insert into user
(
  user_id,
  customer_id,
  create_user,
  update_user
)
values
(
  sysuserid,
  hwcustid,
  sysuserid,
  sysuserid
);

insert into customer
(
  customer_id,
  create_user,
  update_user
)
values
(
  hwcustid,
  sysuserid,
  sysuserid
);

commit;

end;

I try to run the script in psql and thru pgadmin and cannot seem to make
them work.  I do not want to turn it into a function.  I just want it to
execute the block in a fashion similar to Oracle'sqlplus running
@scriptfile.sql.

Thanks

[SQL] update using recursion

[Steven Dahlin]

Is it possible to execute an update using recursion?  I need to update
a set of records and also update their children with the same value.
I tried the following query but it gave an error at the "update
schema.table tbl":

    with recursive childTbl( pid,
 ppid,
 proc_id,
 other_id )
 as  ( select prc.pid,
  prc.ppid,
  prc.proc_id,
  prc.other_id
    from  my_schema.prc_tbl   prc
    where ( ( prc.proc_path  like '%stuff%' )
  or    ( prc.proc_parameters    like '%stuff%' ) )
 and  ( prc.other_id is null )
   union all
   select prcsub.pid,
  prcsub.ppid,
  prcsub.proc_id,
  prcsub.other_id
    from  childTbl    prcpar,
  my_schema.prc_tbl   prcsub
    where ( prcsub.ppid = prcpar.pid )
 )
   update my_schema.prc_tbl  prc
 set   other_id   = 101
 from  childTbl

However, if I do a "select * from childTbl" it works.  The docs take
about updates and talk about recursive queries with selects but
nothing seems to cover the joining of the two.

Thanks

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] update with recursive query

[Steven Dahlin]

Is it possible to execute an update using recursion?  I need to update a set
of records and also update their children with the same value.  I tried the
following query but it gave an error at the "update schema.table tbl":

with recursive childTbl( pid,
 ppid,
 proc_id,
 other_id )
 as  ( select prc.pid,
  prc.ppid,
  prc.proc_id,
  prc.other_id
from  my_schema.prc_tbl   prc
where ( ( prc.proc_path  like '%stuff%' )
  or( prc.proc_parameterslike '%stuff%' ) )
 and  ( prc.other_id is null )
   union all
   select prcsub.pid,
  prcsub.ppid,
  prcsub.proc_id,
  prcsub.other_id
from  childTblprcpar,
  my_schema.prc_tbl   prcsub
where ( prcsub.ppid = prcpar.pid )
 )
   update my_schema.prc_tbl  prc
 set   other_id   = 101
 from  childTbl

However, if I do a "select * from childTbl" it works.  The docs take about
updates and talk about recursive queries with selects but nothing seems to
cover the joining of the two.

Thanks

[SQL] getting PSQLException Can't infer the SQL type to use with Native Query call

[Steven Dahlin]

In trying to call a postgresql 8.4 stored function I am passing a
class as well as a List (I have to save a master record as well as the
detail records). This is being done with a native query using
EclipseLink 2.1. However, I am getting back a message for the first
item:

PSQLException: Can't infer the SQL type to use for an instance of
com.hwcs.veri.shared.dto.MyClass. Use setObject() with an explicit
Types value to specify the type to use.

I am calling the query setting up the parameter with:

query.setParameter( 1, myClass );

The receiving stored function has the following:

 FUNCTION perform_schema.saveInfo( newrecboolean,
   myClass   ,
   myDtl [] )

I am unclear how to setup the pass fromm eclipselink to postgresql.
Does anyone have any suggestions or can point to docs which speak to
this specifically?

Thanks

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql