Re: Passing a table to function

sqlguru wrote:
> In SQL 2008, we could pass tables into stored procedures.
> CREATE TABLE members -- Only username is required
> (
>      mem_username VARCHAR(25) NOT NULL PRIMARY KEY,
>      mem_email VARCHAR(255),
>      mem_fname VARCHAR(25),
>      mem_lname VARCHAR(25)
> );
>
> CREATE TABLE TYPE member_table_type
> (
>        mem_username VARCHAR(25)
> );
>
> CREATE STORED PROCEDURE CreateMembers
>          @members member_table_type READONLY
> AS
>      INSERT INTO [members]
>      SELECT * FROM @members;

OK - so it's binding "mem_username" from your type to the same-named
column in members.

> To execute this stored procedure, you would do:
> DECLARE @members member_table_type;
> INSERT INTO @members (mem_username)
> VALUES( ('mem1'), ('mem2'), ('mem3') );
> EXECUTE CreateMembers @members;
>
>
> How would you accomplish this on Postgre 8.4? I know you can pass an
> entire row to a function but that is not what I want. Notice that even
> though the table has many columns (nullable), I'm only passing in the
> username.

Well, you defined a type with just the one column.

 > With the ROW datatype in Postgre, you have to pass in all
> the columns (null if no value).

I'm guessing you're not puzzled about doing:

CREATE TYPE member_table_type AS (
   mem_username VARCHAR(25)
);

...
INSERT INTO members (mem_username) VALUES (var_members.mem_username);
...

Perhaps the closest to duplicating the exact way you're doing it in
MS-SQL 2008 would be by passing in a cursor. The code below shows that
(although it's not the same as your example).

= begin script =

CREATE TABLE test_tbl(a int4, b text);
INSERT INTO test_tbl VALUES (1,'a');
INSERT INTO test_tbl VALUES (2,'b');
INSERT INTO test_tbl VALUES (3,'c');

CREATE FUNCTION test_cursors(c refcursor) RETURNS integer AS $$
DECLARE
     tot integer;
     r   RECORD;
BEGIN
     tot := 0;
     LOOP
         FETCH c INTO r;
         EXIT WHEN NOT FOUND;
         tot := tot + r.a;
     END LOOP;

     RETURN tot;
END;
$$ LANGUAGE plpgsql;

DECLARE mycursor CURSOR FOR SELECT * FROM test_tbl;

SELECT sum(a) FROM test_tbl;
SELECT test_cursors('mycursor');

= end =

The other way would be to create a TEMPORARY table, pass its name and
use EXECUTE inside the plpgsql to generate the INSERT statement you require.

--
   Richard Huxton
   Archonet Ltd