Re: [GENERAL] NEED URGENT HELP....

[Sandip G]

  Great !!!  Its working Thanks to all for the great help. I am new
  to Postgre and like it's performance... I would like to learn it in
  depth, Please provide me any good resource to learn Postgre with lots
  of sample code/queries. Cheers !!!Best Regards,Sandip.

  - Original Message -
  From: Gurjeet Singh
  To: Martijn van Oosterhout , Henrique P Machado , Guy Rouillier
  , pgsql-general@postgresql.org, Sandip G
  Subject: Re: [GENERAL] NEED URGENT HELP
  Date: Tue, 26 Dec 2006 20:39:01 +0530

  It works Martijn...

  but with a few changes will be required in your function Sandip; you
  will have to pass an ARRAY constructor and return a SETOF record.
  Here's a sample:

  postgres= create table tab ( a int, b int );
  CREATE TABLE
  postgres= insert into tab values ( 1, 9 );
  INSERT 0 1
  postgres= insert into tab values (2,8);
  INSERT 0 1
  postgres= insert into tab values (3,7);
  INSERT 0 1
  postgres= insert into tab values (4,6);
  INSERT 0 1
  postgres= insert into tab values (5,5);
  INSERT 0 1
  postgres= create or replace function fun ( character varying [] )
  returns setof
   tab as
  postgres- $$
  postgres$ select * from tab where a = any ($1)
  postgres$ $$ language 'sql' volatile;
  CREATE FUNCTION
  postgres= select fun('{1}');
  fun
  ---
   (1,9)
  (1 row)

  postgres= select fun('{2,3}');
  fun
  ---
   (2,8)
   (3,7)
  (2 rows)

  postgres=

  Hope it helps


  On 12/26/06, Martijn van Oosterhout kleptog@svana.org wrote:

On Mon, Dec 25, 2006 at 08:52:52PM -0300, Henrique P Machado
wrote:
 WHERE   (USER_ID = $1) AND (COMPANY_ID = $2) AND
BOOK_NO IN ($3)

 Could'nt he use an array in this 3rd parameter?

I think so, if it's written:

AND BOOK_NO = ANY($3)

Have a nice day,
--
Martijn van Oosterhout   kleptog@svana.org  
http://svana.org/kleptog/
 From each according to his ability. To each according to his
ability to litigate.


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFkQ1LIB7bNG8LQkwRApbCAJsH26IcDusO5Vi5kNC1UQ185usbnACeOxdC
xQo+z5Z7+Xofks/h3MmeF7w=
=Rq6g
-END PGP SIGNATURE-





  --
  [EMAIL PROTECTED]
  [EMAIL PROTECTED] gmail | hotmail | yahoo }.com

-- 
___
Search for products and services at:
http://search.mail.com

[GENERAL] NEED URGENT HELP....

[Sandip G]

  I am using PostgreSql 8.1 with pgAdmin III. OS is XP. this is my
  function:

  CREATE OR REPLACE FUNCTION sp_get_phase(character varying, character
  varying, character varying)
  RETURNS ret_dv_sp_get_phase AS
  $BODY$
  SELECT  BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
  UPDATE_DATE,
  AddInfo1, AddInfo2
  FROM  T_PHASE
  WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
  $BODY$
  LANGUAGE 'sql' VOLATILE;


  When I run
  select * from sp_get_phase ('sandip', 'oms', '4') returns 1
  record.this works fine

  select * from sp_get_phase ('sandip', 'oms', '1')  returns 1
  record.this also works fine... BUT

  select * from sp_get_phase ('sandip', 'oms', '1,4') this return a
  Blank record.

  I tried to execute the SQL statement from the function

  SELECT  BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
  UPDATE_DATE,
  AddInfo1, AddInfo2
  FROM  T_PHASE
  WHERE (USER_ID = 'testUser') AND (COMPANY_ID = 'testCompany') AND
  BOOK_NO IN (1,4)

  - This Works fine... returns 2 records. What may be the problem?

  Thanks in advance.
  Regards,
  Sandip.

-- 
___
Search for products and services at:
http://search.mail.com