[SQL] Dynamic Query
Hi, I'm a novice PostgreSQL developer from an Oracle background and am trying to replicate some Oracle functionality in PostgreSQL / plpgSQL. I'm trying to write a stored function to implement a search: the function has several parameters - the value of any could be 'null' on any given invocation, indicating that this parameter does not represent a data item being searched on. In Oracle, this could be implemented as follows - this implementation copes with missing values and allows the user of bind variables - helping to guarantee performance and also providing protection against SQL Injection: FUNCTION fnGetStandardUsers ( p_in_aur_username IN VARCHAR2 , p_in_is_account_enabled IN VARCHAR2 ) RETURN SYS_REFCURSOR IS l_SQL VARCHAR2(32767 CHAR) DEFAULT ' SELECT ' || 'vsaur.aur_id id ' || ' , vsaur.aur_username ' || ' , vsaur.aur_is_account_enabled ' || ' FROM ' || ' app_data.v_standard_app_user vsaur ' || ' WHERE ' || '1 = 1 '; BEGIN IF p_in_aur_username IS NOT NULL THEN l_SQL := l_SQL || ' AND vsaur.aur_username LIKE ''%''||:p_in_aur_username||''%'' '; ELSE l_SQL := l_SQL || ' AND (1 = 1 OR :p_in_aur_username IS NULL) '; END IF; OPEN l_dataSet FOR l_SQL USING UPPER(p_in_aur_username); RETURN l_dataSet; END fnGetStandardUsers; Is there a recommended way to translate this function into plpgSQL which would protect me from SQL Injection (most important for me) and use bind variables (of secondary importance? The postgresql documentation seems to suggest that I can use the RETURN QUERY EXECUTE feature, or simply build my query with a string and execute it (I don't see how the latter can protect me from SQL Injection though???) Any help would be appreciated! Thanks, Andrew _ Use Windows Live Messenger for free on selected mobiles http://clk.atdmt.com/UKM/go/174426567/direct/01/
[SQL] Table Valued Parameters
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 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 motivation for doing this is to reduce network round trips, instead of having 1 call per record to be sent to the db, I can have 1 call passing all values which I wish to store in the database. Sending multiple records to the database as a result of a single form submission is a requirement that arises frequently [the example is just intended to demonstrate the principle!], and I would be grateful if anybody could help me to arrive at an optimal solution. Cheers, Andrew. _ Download Messenger onto your mobile for free http://clk.atdmt.com/UKM/go/174426567/direct/01/
FW: [SQL] Table Valued Parameters
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. 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 motivation for doing this is to reduce network round trips, instead of > > having 1 call per record to be sent to the db, I can have 1 call passing all > > values which I wish to store in the database. > > > > Sending multiple records to the database as a result of a single form > > submission is a requirement that arises frequently [the example is just > > intended to demonstrate the principle!], and I would be grateful if anybody > > could help me to arrive at an optimal solution. > > > > Cheers, > > > > Andrew. > > > > > > > > > > > > > > > > Download Messenger onto your mobile for free. Learn more. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql Chat to your friends for free on selected mobiles. Learn more. _ Chat to your friends for free on selected mobiles http://clk.atdmt.com/UKM/go/174426567/direct/01/
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
[SQL] PostgreSQL Security/Roles/Grants
Hi, I come from an Oracle background and wonder if anyone could provide some background information on how best to implement an application security scheme in PostgreSQL using roles / grants. I'd just like to outline first how I'd approach security in Oracle: There is a notion of both: 1. Default Roles -> a role which is activated at login time. Oracle imposes a limit on the number of default roles which any given user can have. 2. Non-default role -> a role which has to be explicitly activated during the lifecycle of an application in order to gain access to database resources. There are no limits on the number of non-default roles. This type of role helps us to only provide a user with the minimal set of privileges that they require at any given time, and minimise their access to database resources. I have looked through the PostgreSQL documentation, and cannot find anything analogous to the 'non-default role' which I have outlined above - although obviously it does support roles. I just want to confirm that all roles in postgreSQL are activated at login time? Secondly, is there a limit on the number of roles which can be assigned to a user (or more accurately a 'login role') in postgreSQL? Many thanks, Andrew. _ New Windows 7: Find the right PC for you. Learn more. http://www.microsoft.com/uk/windows/buy/
[SQL] Foreign key - Indexing & Deadlocking.
Hi, I'm porting some Oracle software to PostgreSQL, & have a question about best practices with foreign keys. In the Oracle environment, you're basically obliged to index all foreign keys (more specifically foreign key columns should appear on the leading edge of an index) due to the way that it manages locks. Failure to index foreign keys in this way in Oracle often causes deadlock situations. Although I could no find a mention of anything similar in the postgreSQL documentation, I just wanted to check whether or not the same was true of PostgreSQL to be on the safe side. Thanks, Andrew. _ Use Hotmail to send and receive mail from your different email accounts http://clk.atdmt.com/UKM/go/186394592/direct/01/
