Re: [SQL] How to order varchar data by word
Hello, Adrian. Thank you for your email. I am not using any SQL at this time, but I 'd like to understand what is exactly the criterion that the ORDER BY uses to order text. Giving you some background : I need to make an efficient join between data generated by my program and data output by postgres and of course, it is mandatory that both lists of records be ordered using the same criterion. I need to figure out what is exactly the criterion used by postgres so I can mimic it on my program. I thought it was ordinal, but it seems it isnt, as the @ symbol comes before the 0 (zero). According to ASCII numeric codes, it shouldn't If any one can explain me exactly how the order by clause works on varchars, I 'd really appreciate it. I've already examined documentation carefully, but couldn't find it. Maybe I looked on the wrong place... Best, Oliveiros - Original Message - From: "Adrian Klaver" To: Cc: "Oliveiros C," Sent: Wednesday, October 14, 2009 9:54 PM Subject: Re: [SQL] How to order varchar data by word On Wednesday 14 October 2009 7:13:22 am Oliveiros C, wrote: Hello, list. I have a table with a varchar field that I would like to order by word, not by ordinal, which seems to be the default on postgres. Does anyone have a clue on how this can be done? Many thanx in advance, Best, Oliveiros Can you show the SQL you are using? -- Adrian Klaver [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to order varchar data by word
"Oliveiros C," writes: > If any one can explain me exactly how the order by clause works on varchars, > I 'd really appreciate it. It sorts according to the sort order imposed by your locale setting (see LC_COLLATE in particular). A lot of locales have weird and wonderful rules that embody somebody's notion of "dictionary order". If you don't like it, try switching to C locale. Or you could learn enough about locale definitions to create your own. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Efficiency in Multiple Queries of Same Table in a PL/PgSQL Function
> But if I read the OP correctly the sigma are in fact used additively in each > row in blah. "sigma_* = sigma_* +" I apologize, but I omitted a CASE statement prior to each calculation of the values. The coefficients for each calculation change depending upon which case is valid. I could probably rewrite it using arrays and may, in fact, do so to eliminate the two added queries. Thank you for your input. -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[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/
Re: [SQL] Dynamic Query
Andrew Hall writes: > 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? See quote_literal() and/or quote_nullable(). On the whole though I think you'd be best off not using a dynamically-constructed query at all --- given the desired %'s in the LIKE pattern, there is not going to be any benefit at all from using an unparameterized query. Just write it out without all the string-construction. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
