Re: Passing a table to function

On Mon, Jul 6, 2009 at 7:27 AM, sqlguru<sqlguru(at)live(dot)com> 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;
>
> 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. With the ROW datatype in Postgre, you have to pass in all
> the columns (null if no value).
>
> This is what I have so far in Postgre:
> CREATE FUNCTION create_members(IN var_members members)
> BEGIN
>     INSERT INTO members
>     SELECTvar_members.mem_username, var_members.mem_email,
> var_members.mem_fname, var_members.mem_lname;
> END
>
> SELECT create_members(ROW('mem1', NULL, NULL, NULL));

I prefer an explicit cast using the specific type:
SELECT create_members(('mem1', NULL, NULL, NULL)::members);

> INSERT INTO members
> SELECTvar_members.mem_username, var_members.mem_email,
> var_members.mem_fname, var_members.mem_lname;

This isn't necessary if you are using the table type. Prefer:

INSERT INTO members select (var_members).*;

Also, in 8.4, if you were wanting to pass one or more 'members'
records into the function for multiple create, you could modify or
overload the function to take an array of members.

create or replace function create_members(_members members[]) ...
...
INSERT INTO members select (m).* from (select unnest(_members) as m) q;

also, some style tips:
*) 'IN' is optional...I'd leave it out.
*) var_ prefix is pretty verbose, i'd prefer '_' or 'i_' (i being in)
*) use plurals for arrays, singular for tables.

merlin

merlin