CODEWITHSUNDEEP
postgresqlfunctionplpgsql
Torenaga
Torenaga

Reputation: 25

Create a function with an argument as a subselect

I'd like to create a function for select and changed me data

CREATE OR REPLACE FUNCTION PublicatedTask( argument ) RETURNS SETOF task AS $$DECLARE
   f task%ROWTYPE;   
  BEGIN
    FOR f IN SELECT * FROM Task where layer IN $1 and publicationin<>0 ORDER BY id LOOP
      if (f.publicationIN = 1) then
    f.description='';
    end if;    
    RETURN NEXT f; 
    END LOOP;
    RETURN;
  END;
  $$
 LANGUAGE 'plpgsql';

but I 'dont know what argument type?

I'd like to do SELECT * FROM PublicatedTask((1,2,3));

Thanks for your help

Upvotes: 1

Views: 284

Answers (2)

Frank Heikens
Frank Heikens

Reputation: 127297

Or use VARIADIC:

CREATE OR REPLACE FUNCTION PublicatedTask( VARIADIC argument int[]) RETURNS SETOF task AS $$DECLARE
   f task%ROWTYPE;   
  BEGIN
    FOR f IN SELECT * FROM Task where layer = ANY($1) and publicationin<>0 ORDER BY id LOOP
      if (f.publicationIN = 1) then
    f.description='';
    end if;    
    RETURN NEXT f; 
    END LOOP;
    RETURN;
  END;
  $$
 LANGUAGE 'plpgsql';

And use it this way:

SELECT * FROM PublicatedTask(1,2,3);

VARIADIC is available as of version 8.4: http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS

Upvotes: 1

Diogo Biazus
Diogo Biazus

Reputation: 622

you could use an array of integers as parameter:

CREATE OR REPLACE FUNCTION PublicatedTask( argument int[]) RETURNS SETOF task AS $$DECLARE
   f task%ROWTYPE;   
  BEGIN
    FOR f IN SELECT * FROM Task where layer = ANY($1) and publicationin<>0 ORDER BY id LOOP
      if (f.publicationIN = 1) then
    f.description='';
    end if;    
    RETURN NEXT f; 
    END LOOP;
    RETURN;
  END;
  $$
 LANGUAGE 'plpgsql';

Then you could call it this way:

SELECT * FROM PublicatedTask('{1,2,3}');

Upvotes: 0

Related Questions