[GENERAL] How do write schema independent install files for functions.
Hi I have a number of functions which I wish to wrap up in a SQL script (well technically DDL script). The functions reference one another and for safety it is necessary for them to ether set the search_path or directly reference the schema for one another. I was wondering if there is good / recommended way to write the install script to easily install to an arbitrary schema. The problem I have is that some functions need to set the search_path for code security. As an example below, the function bar() needs to set search_path or it would accidently reference the wrong foo(). However in order to install the same functions to a different schema I would have to re-write the script with numerous changes to the various set search_path = my_schema lines. SET search_path = my_schema; CREATE OR REPLACE FUNCTION foo() RETURNS INTEGER AS $BODY$ BEGIN RETURN 42; END; $BODY$ LANGUAGE plpgsql IMMUTABLE COST 100; CREATE OR REPLACE FUNCTION bar() RETURNS INTEGER AS $BODY$ BEGIN RETURN foo(); END; $BODY$ LANGUAGE plpgsql IMMUTABLE SET search_path = my_schema COST 100; Is there any more flexible way to do this? Thanks -- 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] How do write schema independent install files for functions.
On 16/07/2012 20:44, Tom Lane wrote: Philip Couling p...@pedal.me.uk writes: I have a number of functions which I wish to wrap up in a SQL script (well technically DDL script). The functions reference one another and for safety it is necessary for them to ether set the search_path or directly reference the schema for one another. I was wondering if there is good / recommended way to write the install script to easily install to an arbitrary schema. CREATE FUNCTION's SET search_path FROM CURRENT option might help you. regards, tom lane Thanks tom. That was exactly what I was looking for and what I'd missed in the manual. For the sake of future readers FROM CURRENT is documented here: http://www.postgresql.org/docs/current/static/sql-createfunction.html Regards -- 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] Feature discussion: Should syntax errors abort a transaction?
On 20/06/2012 08:24, Chris Travers wrote: It seems to me there is one very simple reason not to change current behavior which those in favor are glossing over. Most interactions with a database are not occurring over an interface like psql with one person typing on one side and the db executing on the other.If that were the case I would understand the concern that a typo should give the user an opportunity to pick up the statement where he/she left off. However most interactions with the database are purely through intermediary software. Adding a lot of do what I mean or give me a chance to retry that adds a great deal of complexity to the job of the software in trapping and handling errors. It is far, far more simple to say syntax errors abort transactions and leave it at that. I know as a developer I don't want that behavior to change. I guess it seems to me that I would not object to a new option for transaction behavior where one could do something like SET TRANSACTION INTERACTIVE; and have no errors abort the transaction at all (explicit commit or rollback required) but I would complain loudly if this were to be the default, and I don't see a real need for it. Best Wishes, Chris Travers It would be very nice to turn this feature off completely as a property of your session. I generally see it as necessary to do everything inside a transaction when working in the DB manually. It adds greater protection against forgotten WHERE clauses etc. I've seen too many DBs mashed because of a careless typo. The current behavior encourages admins not to use transactions because any error (typo or not) forces them to re-do all their work so far or put in a lot of extra typing to wrap everything. On the idea of different error behavior between bad syntax and pragmatics... Splitting hairs between a syntax error and other errors is dangerous. There are too many cases where the division can not be clear. And any implementation would find it difficult not to fall foul of the principle of least astonishment. http://en.wikipedia.org/wiki/Principle_of_least_astonishment For example pg/plsql executing dynamic SQL. An error may have been caused by faulty arguments. However one of the arguments may have been a SQL statement in part or full. How should PostgreSQL behave? See the argument as bad (data error) or the SQL it contains as a syntax error. You can always find an answer to this that works, but will that answer be obvious to every developer? Regards Phil -- 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] || versus concat( ), diff behavior
On 02/03/12 20:58, david.sahag...@emc.com wrote: Can anybody please point me to where this difference of behavior is explained/documented ? Thanks, -dvs- -- version = 9.1.3 do $$ declare v_str char(10); begin v_str := 'abc' ; raise info '%', concat(v_str, v_str) ; raise info '%', v_str||v_str ; end $$; INFO: abc abc INFO: abcabc Concat is a function which concatenates whatever you give it blindly. Hence it has the behavior that includes the blanks. The || operator reflects the more general PostgreSQL principle that trailing blanks are insignificant for char fields. You see the same behavior when comparing char variables. This can be found in the manual: http://www.postgresql.org/docs/current/static/datatype-character.html Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way. However, the padding spaces are treated as semantically insignificant. Trailing spaces are disregarded when comparing two values of type character, and they will be removed when converting a character value to one of the other string types. Note that trailing spaces are semantically significant in character varying and text values, and when using pattern matching, e.g. LIKE, regular expressions. Hope this makes it just a little clearer. Regards -- 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] Easy form of insert if it isn't already there?
This must be a function or trigger to break one statement into two. You could of course simply use two separate statements in PHP as long as they are in the same transaction. If you're going to perform this action in two steps then putting both in a function or trigger is often preferable. Looking back at your original question. Although your option 2 feels chunky, it feels to me a generally better option. INSERT INTO table_name (col1,col2,col3) SELECT val1,val2,val3 WHERE NOT EXISTS (SELECT * FROM table WHERE col1=val1 AND col2=val2) or INSERT INTO table (col1,col2,col3) SELECT val1,val2,val3 WHERE (val1,val2,val3) NOT IN (SELECT col1,col2,col3 FROM table) It does what it says on the tin and someone else maintaining your code will understand what it does at a glance. The same can not be said for triggers and perhaps functions. My gut feeling is that the performance of this will be better too. Regards, Phil On 15/02/2012 07:14, Bartosz Dmytrak wrote: Yes it is. You can implement trigger on table to check if inserted record is new. Still it is on DB side. I don't know PHP well enough but I think You can call function e.g. SELECT myschema.InsertWhenNew (val1, val2, val3); in the same way as You call INSERTS Regards, Bartek 2012/2/15 Chris Angelico ros...@gmail.com mailto:ros...@gmail.com On Wed, Feb 15, 2012 at 5:26 PM, Bartosz Dmytrak bdmyt...@eranet.pl mailto:bdmyt...@eranet.pl wrote: Hi, similar topic is in NOVICE mailing list: http://archives.postgresql.org/pgsql-novice/2012-02/msg00034.php e.g. You can use BEGIN... EXCEPTION END, good example of such approach is there: http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE; Ah, thanks for that! Currently the query is a single PHP pg_query_params() call, and it's inside a larger transaction. By the look of it, this requires writing a function to do the job, rather than embedding the logic straight into the query - is this correct? ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] last entry per person
On 03/02/2012 08:30, Chris Travers wrote: On Fri, Feb 3, 2012 at 12:26 AM, garry ga...@scholarpack.com mailto:ga...@scholarpack.com wrote: I have a table which holds a user name and their results in exams. There can be multiple entries per user. I am trying to return the last entry for each user. I can get the last entry in the table using the order by/limit method but how would this be applied per user. My table definition is like the following: gradeid serial primary key, user text, grade char(1), entered timestamp, Any help would be appreciated. Regards What about a combination of a common table expression and a windowing function? You ought to be able to order by your criteria and then pull where the rowcount in the window is 1. Best Wishes, Chris Travers Garry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general There is no simple solution like there is for the last row in the table. Window functions are one option, sub queries are another: SELECT * FROM results, ( SELECT student_id, max(result_date) result_date FROM results GROUP BY student_id ) as latest WHERE results.student_id = latest.student_id AND results.result_date = latest.result_date Note that for this to work correctly, result_date will need to be unique. Regards
