Re: [SQL] Problem with return type of function ??? (corrected)
Tom Lane a écrit : > Denis BUCHER writes: >> To do this it will be a little complicated because of table >> dependencies... And it could bug again at the next DROP COLUMN... Is >> there a way to change my function (RETURN SETOF part) to specify the >> column names/types ? > > No, not really. You could maybe un-drop the columns with some manual > surgery on pg_attribute, but it doesn't seem like that's going to lead > to a nice solution. > > If you were really desperate you could try back-porting the patch: > http://archives.postgresql.org/pgsql-committers/2009-08/msg00068.php > but I wouldn't want to vouch for its safety, considering it hasn't > been through a beta test cycle yet. Finally there was no real dependencies of that table, and I've found out that the "dependency" was my function ! Using SETOF (table) makes impossible to DROP the table. Therefore I DROP my function and was able to follow you advice, and it worked perfectly ! Thanks a lot for your help (as well as Richard's) Denis -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Valued Parameters
Thanks Bruce, what I was looking for was the postgreSQL equivalent of table-valued parameters from SQL Server 2008 (http://technet.microsoft.com/en-us/library/bb510489.aspx) or tables of Oracle Objects from Oracle. (Or something that would allow me to achieve the same effect). The example that you've provided only allows a 'type' variable containing 1 record to be submitted to a plpgsql procedure per invocation of that procedure. Anyhow, Pavel Stehule has kindly explained that while there is no exact equivalent in postgreSQL - but has recommended that I investigate the array functionality, and the COPY command. In retrospect, I should of just asked how one would go about submitting multiple records of the same type/signature to a plpgsql procedure with a single invocation (of that plpgsql procedure) from a client application. All the same - I would like to express my thanks to you for taking the time to suggest an approach. Cheers, Andrew. > Date: Fri, 23 Oct 2009 20:32:37 +0200 > Subject: Re: FW: [SQL] Table Valued Parameters > From: br...@zwartberg.com > To: andre...@hotmail.com > CC: pgsql-sql@postgresql.org > > 2009/10/23 Andrew Hall : > > Hi Pavel, > > > > many thanks for the very prompt reply. > > > > I was under the impression that a refcursor was a pointer to a dataset > > already resident on the database, and were used to return a reference to a > > dataset resident in the database to a client application. > > > > What I am trying to do is build a table valued variable in a client > > application then submit it to a stored procedure resident on the database, > > and have that stored procedure manipulate it as though it were a table [be > > it inserting, updating or deleting based upon its contents]. > > > > Is this possible? > > > > I apologise for not making my question more clear. > > Is the following too simplistic (maybe I have not understood your > question either, but it seems that postgresql makes it so simple, that > "problems" you had to solve in ORACLKE, aren't a "problem" in > postgresql.) > > create type ty_app_user as ( > aur_id integer, > ... etc > ); > > create or replace function prAddUsers ( p_in_users tty_app_user ) > returns void as $$ > declare > begin > insert into users (aur_id ... etc ) values (p_in_users.aur_id, etc...); > end; > $$ language plpgsql; > > > > Thanks, > > > > Andrew. > > > >> Date: Fri, 23 Oct 2009 20:10:48 +0200 > >> Subject: Re: [SQL] Table Valued Parameters > >> From: pavel.steh...@gmail.com > >> To: andre...@hotmail.com > >> CC: pgsql-sql@postgresql.org > >> > >> Hello > >> > >> 2009/10/23 Andrew Hall : > >> > Hi, > >> > > >> > I was wondering whether anybody would be able to advise me on how (if it > >> > is > >> > possible) to port some functionality from Oracle? > >> > > >> > This is just an example - in Oracle, I am able to do the following > >> > > >> > >> Use refcursor, please. > >> > >> http://www.postgresql.org/docs/8.4/static/plpgsql-cursors.html > >> > >> Regards > >> Pavel Stehule > >> > >> > -- > >> > -- Create a data type which replicates the data structure of a single > >> > user > >> > in my application. > >> > -- I know that this can be done using PostgreSQL. > >> > -- > >> > > >> > CREATE TYPE TY_APP_USER AS OBJECT > >> > ( > >> > aur_id INT > >> > , aur_username VARCHAR2(30 CHAR) > >> > , aur_is_account_enabled VARCHAR2(1 CHAR) > >> > , aur_created_date DATE > >> > , aur_updated_date TIMESTAMP > >> > ) > >> > / > >> > > >> > -- > >> > -- Create a data type which can store many instances of a single > >> > 'TY_APP_USER' > >> > -- [essentially this is a table valued data type]. An instance of this > >> > data > >> > type can be > >> > -- created and populated by the client application [a java based one in > >> > my > >> > case]. > >> > -- > >> > -- I can't find any reference to something > >> > -- similar to this using postgreSQL. > >> > -- > >> > > >> > CREATE TYPE TTY_APP_USER AS TABLE OF TY_APP_USER > >> > / > >> > > >> > -- > >> > -- Next define a stored procedure which can accept an instance of a > >> > TTY_APP_USER data > >> > -- type, and treat that instance as a table, for example ... > >> > -- > >> > > >> > CREATE OR REPLACE PROCEDURE prAddUsers > >> > ( > >> > p_in_users IN tty_app_user > >> > ) > >> > IS > >> > BEGIN > >> > > >> > INSERT > >> > INTO > >> > users > >> >( > >> > aur_id > >> >, aur_username > >> >, aur_is_account_enabled > >> >, aur_created_by > >> >, aur_created_date > >> >) > >> > SELECT > >> > aur_id > >> >, aur_username > >> >, aur_is_account_enabled > >> >, aur_created_by > >> >, aur_created_date > >> > FROM > >> > TABLE > >> > ( > >> >CAST > >> >( > >> > p_in_users AS tty_app_user > >> >) > >> > ); > >> > > >> > END prUpdateUsers; > >> > > >> > My m
Re: [SQL] Table Valued Parameters
Andrew Hall wrote: > Hi, > > I was wondering whether anybody would be able to advise me on how (if it is > possible) to port some functionality from Oracle? > > This is just an example - in Oracle, I am able to do the following > > -- > -- Create a data type which replicates the data structure of a single user in > my application. > -- I know that this can be done using PostgreSQL. > -- Create a data type which can store many instances of a single > 'TY_APP_USER' > -- [essentially this is a table valued data type]. An instance of this data > type can be > -- created and populated by the client application [a java based one in my > case]. > -- > -- I can't find any reference to something > -- similar to this using postgreSQL. The following may not do anything interesting, but it does show arrays of composite types, which is what you are after. To prevent quoting insanity, I recommend the ARRAY[] constructor rather than array literals. You do need the explicit typecasts. Oh - and version 8.3 or higher for arrays of compound types. BEGIN; CREATE TYPE typ1 AS (i integer, t text); CREATE FUNCTION print_array(a typ1[]) RETURNS void AS $$ DECLARE m int; n int; i int; e typ1; BEGIN m := array_lower(a, 1); n := array_upper(a, 1); FOR i IN m .. n LOOP e := a[i]; RAISE NOTICE '% - %', e.i, e.t; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; SELECT print_array(ARRAY[ '(1,"abc")'::typ1, '(2,"def")'::typ1 ]); SELECT print_array(ARRAY[ '(1,"abc")', '(2,"def")' ]::typ1[]); ROLLBACK; -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Valued Parameters
2009/10/24 Andrew Hall : > Thanks Bruce, > > what I was looking for was the postgreSQL equivalent of table-valued > parameters from SQL Server 2008 > (http://technet.microsoft.com/en-us/library/bb510489.aspx) or tables of > Oracle Objects from Oracle. (Or something that would allow me to achieve > the same effect). > > The example that you've provided only allows a 'type' variable containing 1 > record to be submitted to a plpgsql procedure per invocation of that > procedure. > > Anyhow, Pavel Stehule has kindly explained that while there is no exact > equivalent in postgreSQL - but has recommended that I investigate the array > functionality, and the COPY command. Maybe you could also use a temporary table, (create temporary table ... on commit drop) > In retrospect, I should of just asked how one would go about submitting > multiple records of the same type/signature to a plpgsql procedure with a > single invocation (of that plpgsql procedure) from a client application. > > All the same - I would like to express my thanks to you for taking the time > to suggest an approach. Its a pleasure. > Cheers, > > Andrew. > >> Date: Fri, 23 Oct 2009 20:32:37 +0200 >> Subject: Re: FW: [SQL] Table Valued Parameters >> From: br...@zwartberg.com >> To: andre...@hotmail.com >> CC: pgsql-sql@postgresql.org >> >> 2009/10/23 Andrew Hall : >> > Hi Pavel, >> > >> > many thanks for the very prompt reply. >> > >> > I was under the impression that a refcursor was a pointer to a dataset >> > already resident on the database, and were used to return a reference to >> > a >> > dataset resident in the database to a client application. >> > >> > What I am trying to do is build a table valued variable in a client >> > application then submit it to a stored procedure resident on the >> > database, >> > and have that stored procedure manipulate it as though it were a table >> > [be >> > it inserting, updating or deleting based upon its contents]. >> > >> > Is this possible? >> > >> > I apologise for not making my question more clear. >> >> Is the following too simplistic (maybe I have not understood your >> question either, but it seems that postgresql makes it so simple, that >> "problems" you had to solve in ORACLKE, aren't a "problem" in >> postgresql.) >> >> create type ty_app_user as ( >> aur_id integer, >> ... etc >> ); >> >> create or replace function prAddUsers ( p_in_users tty_app_user ) >> returns void as $$ >> declare >> begin >> insert into users (aur_id ... etc ) values (p_in_users.aur_id, etc...); >> end; >> $$ language plpgsql; >> >> >> > Thanks, >> > >> > Andrew. >> > >> >> Date: Fri, 23 Oct 2009 20:10:48 +0200 >> >> Subject: Re: [SQL] Table Valued Parameters >> >> From: pavel.steh...@gmail.com >> >> To: andre...@hotmail.com >> >> CC: pgsql-sql@postgresql.org >> >> >> >> Hello >> >> >> >> 2009/10/23 Andrew Hall : >> >> > Hi, >> >> > >> >> > I was wondering whether anybody would be able to advise me on how (if >> >> > it >> >> > is >> >> > possible) to port some functionality from Oracle? >> >> > >> >> > This is just an example - in Oracle, I am able to do the following >> >> > >> >> >> >> Use refcursor, please. >> >> >> >> http://www.postgresql.org/docs/8.4/static/plpgsql-cursors.html >> >> >> >> Regards >> >> Pavel Stehule >> >> >> >> > -- >> >> > -- Create a data type which replicates the data structure of a single >> >> > user >> >> > in my application. >> >> > -- I know that this can be done using PostgreSQL. >> >> > -- >> >> > >> >> > CREATE TYPE TY_APP_USER AS OBJECT >> >> > ( >> >> > aur_id INT >> >> > , aur_username VARCHAR2(30 CHAR) >> >> > , aur_is_account_enabled VARCHAR2(1 CHAR) >> >> > , aur_created_date DATE >> >> > , aur_updated_date TIMESTAMP >> >> > ) >> >> > / >> >> > >> >> > -- >> >> > -- Create a data type which can store many instances of a single >> >> > 'TY_APP_USER' >> >> > -- [essentially this is a table valued data type]. An instance of >> >> > this >> >> > data >> >> > type can be >> >> > -- created and populated by the client application [a java based one >> >> > in >> >> > my >> >> > case]. >> >> > -- >> >> > -- I can't find any reference to something >> >> > -- similar to this using postgreSQL. >> >> > -- >> >> > >> >> > CREATE TYPE TTY_APP_USER AS TABLE OF TY_APP_USER >> >> > / >> >> > >> >> > -- >> >> > -- Next define a stored procedure which can accept an instance of a >> >> > TTY_APP_USER data >> >> > -- type, and treat that instance as a table, for example ... >> >> > -- >> >> > >> >> > CREATE OR REPLACE PROCEDURE prAddUsers >> >> > ( >> >> > p_in_users IN tty_app_user >> >> > ) >> >> > IS >> >> > BEGIN >> >> > >> >> > INSERT >> >> > INTO >> >> > users >> >> > ( >> >> > aur_id >> >> > , aur_username >> >> > , aur_is_account_enabled >> >> > , aur_created_by >> >> > , aur_created_date >> >> > ) >> >> > SELECT >> >> > aur_id >> >> > , aur_username >> >> >
Re: [SQL] Table Valued Parameters
Richard Huxton writes: > To prevent quoting insanity, I recommend the ARRAY[] constructor rather > than array literals. You do need the explicit typecasts. By the same token, you might want to use ROW() rather than composite-type literal syntax for the array elements. > Oh - and version 8.3 or higher for arrays of compound types. I think also that casting the array, rather than the individual rows, only works as of 8.4; ie in 8.3 you have to follow the first example: > SELECT print_array(ARRAY[ '(1,"abc")'::typ1, '(2,"def")'::typ1 ]); > SELECT print_array(ARRAY[ '(1,"abc")', '(2,"def")' ]::typ1[]); In this case there's not much real difference, but with a lot of array elements the individual casts get tedious. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
