Passing a table to function

From: sqlguru <sqlguru(at)live(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Passing a table to function
Date: 2009-07-06 11:27:52
Message-ID: 5de5b661-d0e5-4e95-be7e-cc806bd847ba@t21g2000yqi.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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));

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sebastien FLAESCH 2009-07-06 11:49:48 Normalize INTERVAL ouput format in a db driver
Previous Message Dave Page 2009-07-06 07:33:50 Re: Documentation - PgAdmin