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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
