Thread: pl/pgsql list as parameter.

Hi,
  I have a couple questions, I am tryingto write a function that takes as input a list (size being dynamic) of primaryIDKeys, along with a userdefined type and returns a set of rows containing those keys. Furthermore the rows are exactly (columns) like the table that contains the keys, but has an extra field.((so I define my own tupe)

CREATE FUNCTION somefunc(<how do I declare a list of ints>,my_predef_type ) RETURNS SETOF tp_lm_object  AS '
DECLARE

..........

How do you take in a list of int? And how would one loop through that?


 Much thanks in advance.

-assad

Assad Jarrahian <jarraa@gmail.com> writes:
> How do you take in a list of int?

Use an array.

            regards, tom lane

Hi,

Here is an example

Regards
/David

CREATE OR REPLACE FUNCTION
usp_Inventaire_Transaction_Statut_Changer(int[], varchar, int, date)
RETURNS INTEGER AS '
DECLARE
requestIds ALIAS FOR $1;
companyId ALIAS FOR $2;
targetStatus ALIAS FOR $3;
transactionDate ALIAS FOR $4;
transactionDate_ timestamp;
inventoryTransaction IR%ROWTYPE;
temp RECORD;
itemIds varchar[]:= ''{}'';
BEGIN




IF ( transactionDate IS NOT NULL) THEN
-- Si la date de requˆte est vide ou si c est la date du jour
IF ( date_trunc(''day'', transactionDate) = CURRENT_DATE )
THEN
transactionDate_ := CURRENT_TIMESTAMP;
ELSE
transactionDate_ := transactionDate;
END IF;


Assa
Assad Jarrahian wrote:

> Hi,
> I have a couple questions, I am tryingto write a function that takes
> as input a list (size being dynamic) of primaryIDKeys, along with a
> userdefined type and returns a set of rows containing those keys.
> Furthermore the rows are exactly (columns) like the table that
> contains the keys, but has an extra field.((so I define my own tupe)
>
>CREATE FUNCTION somefunc(<how do I declare a list of ints>,my_predef_type ) RETURNS SETOF tp_lm_object  AS '
>DECLARE
>
> ..........
>
> How do you take in a list of int? And how would one loop through that?
>
>
> Much thanks in advance.
>
> -assad

I am still unclear of how this works. Please help! I really would appreciate this. This is what I have so far:


CREATE TYPE tp_lm_object AS(
 .....
);

CREATE OR REPLACE FUNCTION
getLMs(int[],float(8), float(8)) RETURNS SETOF tp_lm_object AS $$
DECLARE
    myrec record;
    requestIds ALIAS for $1;
    latitude ALIAS for $2;
    longitude ALIAS for $3;
BEGIN
   FOR myrec IN SELECT
   LMID,  LMOrigin ,LMType
   FROM locationMessages
   WHERE LMID IN (requestIDs) LOOP RETURN NEXT myrec; END LOOP; RETURN; END;    
$$ LANGUAGE 'plpgsql';

I have two question

1) how do you call an pgsql function from command line when your function takes an int array?
2) Will the above work. I am not sure, since I think I have to loop twice, one for the myrec and one for the int[]. Can anybody please shed light on this.
3) How do you call a method like this using the CallableStatement in JDBC. I cannot seem to understand how to use setArray() succesfully?

Any help would be appreciated. Much thanks.

-assad