Re: [GENERAL] Passing a table to function
personally they way I do it, is by creating temporary table, in transaction - and use it in function. Obviously that's very indirect, and not obvious if you see function's declaration. But works fine. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Passing a table to function
On Mon, Jul 6, 2009 at 7:27 AM, sqlgurusqlg...@live.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Passing a table to function
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));
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
