Re: [SQL] dynamic columns in a query

Hello 2009/6/11 Jyoti Seth : > Hi All, > > Is there any way in postgres to write a query to display the result in > matrix form. (where column names are dynamic) > look on http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html regards Pavel Stehule > For

Re: [SQL] skip if latter value equal

foo(); regards Pavel Stehule 2009/7/10 Marcin Krawczyk : > Hi list, > > I was wondering if it was possible for a field in SQL query to return NULL > if latter value is exactly the same ? - for given ORDER BY clause, I guess. > For example, query returns: > > xxyy  1  4  tr

Re: [SQL] How update a table within a join efficiently ?

table in FROM clause UPDATE table_a SET table_a.prio = CASE WHEN size >= 10 THEN 1 ELSE 2 END FROM table_b WHERE table_a.table_a_id = table_b.table_a_id; regards Pavel Stehule > > This doesn't work. > But the folowing does, though it looks not efficient with those 3000 SELEC

Re: [SQL] Updating a specific number of rows in pl/pgsql

r on queue, let's call it > queue_id, you should be able to do something like this: > >  UPDATE queue SET assigned = TRUE >  WHERE queue_id IN (SELECT queue_id >    FROM queue >    WHERE id = p_queue_id >    ORDER BY rank >    LIMIT p_number_of_items); > there are one

Re: [SQL] Updating a specific number of rows in pl/pgsql

ng more > than 20. why not? for small number of iteration is loop over cursor good solution. Pavel Stehule > > -- > Peter Headland > Architect > Actuate Corporation > > > -Original Message- > From: Pavel Stehule [mailto:pavel.steh...@gmail.com] > Sent: Tue

Re: [SQL] Selecting values from comma separated string

ows) Time: 0,914 ms regards Pavel Stehule 2009/8/26 Nacef LABIDI : > Hi all, > > I want to write a function that takes as param a comma separated values > string and perform a select matching these values. > > Here is the string '1,3,7,8' > > And I wan to perf

Re: [SQL] Selecting values from comma separated string

oo (my_string) returns setof record as $$ > declare >  sql text; > begin >  sql:='SELECT * FROM my_table WHERE id IN (' || $1 || ')'; >  return query execute sql; > end; > It's dangerous solution - there can be sql injection attack regards Pavel Steh

Re: [SQL] Selecting values from comma separated string

2009/8/26 Nacef LABIDI : > Here I come again to ask how can I pass an array of values to a pgsql > function when I call this function from a delphi program for example. > the driver have to support it. But why? simply you can use varchar and string_to_array function. Pavel > Nacef > > > > On We

Re: [SQL] NEED HELP COPY TO DYNAMIC OUTPUT FILE

Hello COPY in plpgsql are not allowed. regards Pavel Stehule 2009/8/30 Yogi Rizkiadi : > Hi admin, i'm gie from indonesia > > i wanna ask you how to make a dynamic output file from command COPY TO ? > > i have tried this : > > BEGIN > i:=0; > j:=10; > WHI

Re: [SQL] NEED HELP COPY TO DYNAMIC OUTPUT FILE

2009/8/30 Tom Lane : > Pavel Stehule writes: >> COPY in plpgsql are not allowed. > > I think it will work if you use an EXECUTE. > >                        regards, tom lane > I didn't test it. regards Pavel Stehule -- Sent via pgsql-sql mailing list (pgsql-sql@po

Re: [SQL] PostgreSQL Function: how to know the number of the returned results of the Query

Hello look on PERFORM and GET DIAGNOSTICS statements http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS regards Pavel Stehule 2009/9/1 bilal ghayyad

Re: [SQL] selecting latest record

GROUP BY id_product) Regards Pavel Stehule > > Thanks, > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@post

Re: [SQL] @@Error equivalent in Postgresql

ounts(OUT adm_acc_AccountNo character varying, OUT am_acc_AccountNo character varying) RETURNS SETOF record AS $BODY$ select * from "AdminAccounts" full join "AmAccounts" on "adm_acc_AccountNo"

Re: [SQL] @@Error equivalent in Postgresql

can emulate, but any protected block creates inner transaction and this should negative effect on speed - and it are some lines more. http://www.postgres.cz/index.php/PL/pgSQL_%28en%29 Pavel > > Pavel Stehule wrote: >> >> 2009/10/21 maboyz : >>> >>> Hi, >>&

Re: [SQL] Table Valued Parameters

w.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

Re: [SQL] Converting T-SQL to PostgreSQL

d advices: a) don't use camel notation for identifiers b) don't use case sensitive identifiers like "some" c) don't create "SELECT only" functions Why do you use function? use view: CREATE VIEW GetAppAvailability AS SELECT (SELECT "app_Status"

Re: [SQL] Converting T-SQL to PostgreSQL

2009/11/5 Pavel Stehule : > Hello > > 2009/11/5 maboyz : >> >> I am trying to re write the following stored proc as a postgresql >> function..but am new to postgres and it is kind of getting really >> confusing trying to learn and let alone understand the sy

Re: [SQL] LIMIT BASED ON PERCENT

2009/11/18 Guillaume Lelarge : > Le mercredi 18 novembre 2009 à 20:24:09, Another Trad a écrit : >> No, It doesn't. >> In my machine: >> >> First select >> ERROR:  syntax error at end of input >> LINE 1: select * from rapadura.cliente limit 20% >>                                                 ^ >

Re: [SQL] LIMIT BASED ON PERCENT

RETURN NEXT r; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; this code should to work in 8.3 regards Pavel Stehule > > 2009/11/18 Guillaume Lelarge >> >> Le mercredi 18 novembre 2009 à 20:24:09, Another Trad a écrit : >> > No, It doesn't. >> > In my machin

Re: [SQL] LIMIT BASED ON PERCENT

2009/11/18 Lee Hachadoorian : > On Wed, Nov 18, 2009 at 2:30 PM, Pavel Stehule > wrote: >> yes, and don't use 20%. >> >> select * from foo order by somecol limit (select (count(*)*0.2)::int from >> foo) >> >> Regards >> Pavel > > Is thi

Re: [SQL] Sqldf - error message

s expecting the qualifier "dbo_tbl_Broedsels." > to be included in the returned column name.  Either way, you > need to bug sqldf's authors to fix it. > is it Postgres? I see "Error in sqliteExecStatement" regards Pavel Stehule >                        regards, tom l

Re: [SQL] Sqldf - error message

art of the dataframe names, but > still the same problem occurred) I thing, you have a problem with data. Probably some creating script was broken or some similar. The message is clean. Your database has not column BroedselID. Recheck your query, please. Im not sure - maybe SQLite identifiers ar

Re: [SQL] Sqldf - error message

2009/11/20 Pavel Stehule : > Hello > > 2009/11/20 Marvelde, Luc te : >> Hi All! >> >> I just discovered sqldf and im very enthousiastic, as I am a big fan of R >> and I often get frustrated using Access for datamanagement. >> >> I tried running some que

Re: [SQL] Stalled post to pgsql-sql

:33:22.746217+01 | 2009-12-09 11:33:12 (1 row) Time: 21,010 ms regards Pavel Stehule 2009/12/9 sergey kapustin : > Hello, > I need a type that will store date and time, but without seconds and > microseconds (like timestamp does). At least without microseconds. > I also need all the

Re: [SQL] FIND_IN_SET

tring_to_array($2,','),1) g(i) WHERE (string_to_array($2, ','))[i] = $1 UNION ALL SELECT 0 LIMIT 1 $$ LANGUAGE sql STRICT; CREATE OR REPLACE generate_subscripts(anyarray, int) RETURNS SETOF int AS $$ SELECT generate_series(array_lower($1,$2), array_upper($1,$2)) $$ LAN

Re: [SQL] short-cutting if sum()>constant

12) s; sum - 12 -- 12 is eq 12, so test is successful (1 row) Regards Pavel Stehule 2009/12/22 Ivan Sergio Borgonovo : > Hi, > > I'd like to know if > > select sum(qty) from t where status=37; > > is > constant. > > qty is always >0.

Re: [SQL] short-cutting if sum()>constant

2009/12/23 Ivan Sergio Borgonovo : > On Wed, 23 Dec 2009 01:09:40 +0100 > Ivan Sergio Borgonovo wrote: > >> On Wed, 23 Dec 2009 00:00:31 +0100 >> Ivan Sergio Borgonovo wrote: >> >> > On Tue, 22 Dec 2009 20:47:18 +0100 >> > Pavel Stehule wrote: >

Re: [SQL] short-cutting if sum()>constant

2009/12/23 Ivan Sergio Borgonovo : > On Wed, 23 Dec 2009 08:38:52 +0100 > Pavel Stehule wrote: > >> > As even more unexpected... when all row are >0 and most of them >> > are equal to 1 the generate_series performs appreciably better >> > (roughly 15% faster

Re: [SQL] short-cutting if sum()>constant

2009/12/23 Ivan Sergio Borgonovo : > On Wed, 23 Dec 2009 12:52:38 +0100 > Pavel Stehule wrote: > >> The problem is massive cycle. Plpgsql really isn't language for >> this. This interpret missing own arithmetic unit. So every >> expression is translated to SELECT s

Re: [SQL] short-cutting if sum()>constant

2009/12/23 Ivan Sergio Borgonovo : > On Wed, 23 Dec 2009 14:35:27 +0100 > Pavel Stehule wrote: > > >> a) simplicity. PLpgSQL interpret is very simple. >> b) mostly types are little bit different behave than natural C >> types - domains are different, C types doesn&

Re: [SQL] short-cutting if sum()>constant

2009/12/23 Ivan Sergio Borgonovo : > On Wed, 23 Dec 2009 11:36:31 -0500 > Tom Lane wrote: > >> Craig Ringer writes: >> > Pavel Stehule wrote: >> >> these queries are executed in some special mode, but still it >> >> is more expensive than C a = a

Re: [SQL] please help me on regular expression

ent... > you have to use a prefix 'E' - E'some string with \backslash' for your case the reg. expr could be postgres=# select '70.5' ~ e'\\d+\.\\d+'; ?column? -- t (1 row) http://www.postgresql.org/docs/8.1/static/functions-matching.html or postgr

Re: [SQL] very frustrating feature-bug

tatement must be a SELECT. >>> CONTEXT:  SQL function "add_user" >>> >>> SURPRISE :-) SURPRISE :-) >> >> SQL functions are inlined when invoked, and so must be valid subselects. >> >> rewrite it in plpgsql. > > thanx for advice. > &

Re: [SQL] an aggregate to return max() - 1 value?

Hello select min(x) from (select x from data order by x desc limit 2) s; Pavel 2010/3/4 Louis-David Mitterrand : > Hi, > > With builtin aggregates is it possible to return the value just before > max(col)? > > Thanks, > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make

Re: [SQL] Private functions

Hello directly no. There is only workaround - you can verify inside in function content of pg_stat_activity_table - but this have to have a superuser rights. Regards Pavel Stehule 2010/3/13 Gianvito Pio : > Hi all, > is there a way to write a function that can only be called by a

Re: [SQL] Emacs sql-postgres (please, sorry for question not about PostgreSQL).

Hello try to look on http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Terminal.27s_configuration Regards Pavel Stehule 2010/3/18 Dmitriy Igrishin : > Hello all Emacs users! > > I am using Emacs recently. I love sql-mode, to use with PostgreSQL, > but I have problems with i

Re: [SQL] Emacs sql-postgres (please, sorry for question not about PostgreSQL).

2010/3/18 Tom Lane : > Dmitriy Igrishin writes: >> I am using Emacs recently. I love sql-mode, to use with PostgreSQL, >> but I have problems with it. >> When my SQL file (or buffer) are small (50-100 lines) I can send >> it to SQLi buffer without any problems. But when I working with >> large SQL

Re: [SQL] Does IMMUTABLE property propagate?

>        end; >        $$ >                LANGUAGE plpgsql; > yes it should be declared as immutable. plpgsql function is black box for executor, so you have to use some flag. language sql is different, executor see inside, so there you can not do it. Regards Pavel Stehule > c

Re: [SQL] Does IMMUTABLE property propagate?

2010/3/25 Louis-David Mitterrand : > On Thu, Mar 25, 2010 at 08:27:27AM +0100, Pavel Stehule wrote: >> 2010/3/25 Louis-David Mitterrand : >> > On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote: >> >> Petru Ghita writes: >> >> > "..immediat

Re: [SQL] Problem with function returning a result set

QL identifiers. RETURNS TABLE(id integer, full_name text) AS $$ BEGIN RETURN QUERY SELECT e.id, e.first_name||' '||e.last_name FROM employee e WHERE e.last_name LIKE e.name_pattern ||'%'; END $$ LANGUAGE plpgsql; use aliases. Regards Pavel Stehule >

Re: [SQL] understanding select into

ith little bit different syntax. First - target is list of variables or record variable, second - target is table. > Could it be that "_p" is drop automaticly when the function ends?  Something > to do with scope. > _p is just variable regards Pavel Stehule > Could it have so

Re: [SQL] graphing time series data

2010/4/14 Louis-David Mitterrand : > On Wed, Apr 14, 2010 at 08:46:13AM -0700, Richard Broersma wrote: >> On Wed, Apr 14, 2010 at 7:54 AM, Louis-David Mitterrand >> wrote: >> >> > Now, I'd like to make a graph of average prices per week, per >> > id_product. As some prices don't vary much, distrib

Re: [SQL] graphing time series data

2010/4/14 Louis-David Mitterrand : > On Wed, Apr 14, 2010 at 06:06:59PM +0200, Pavel Stehule wrote: >> I don't understand well. Why you don't use a function date_trunc(), >> >> select date_trunc('week', created), count(*) >> from price >> group

Re: [SQL] How to max() make null as biggest value?

- 34 (1 row) regards Pavel Stehule > i.e.  max([1,2,3,4,5]) => 5 > max([1,2,3,4,5,null]) => null > > thanks in advance! > > Feixiong > feixion...@gmail.com > > > > > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >

Re: [SQL] How to max() make null as biggest value?

2010/4/20 Pavel Stehule : > Hello > > 2010/4/14 Feixiong Li : >> Hi , guys , >> >> I am newbie for sql, I have a problem when using max() function, I need get >> null when there are null in the value list, or return the largest value as >> usual, who can do t

Re: [SQL] best paging strategies for large datasets?

rollable cursors. see DECLARE CURSOR statement Regards Pavel Stehule > > But I am intrigued by window functions, especially the row_number() and > ntile(int) ones. > > Adding "row_number() over (order by )" to my query will > return the total number of rows in the first

Re: [SQL] How to get CURRENT_DATE in a pl/pgSQL function

le constant postgres=# CREATE OR REPLACE FUNCTION fo() RETURNS date AS $$ DECLARE d date; BEGIN d := CURRENT_DATE; RETURN d; END; $$ LANGUAGE plpgsql; CREATE FUNCTION Time: 450.665 ms postgres=# select fo(); fo 2010-05-18 (1 row) Regards Pavel Stehule > > Another idea wo

Re: [SQL] How to Insert and retrieve multilingual (Hindi "an Indian language") into PostgreSQL

Hello PostgreSQL doesn't support multilangual tables now - etc it isn't more than one collation per database. But you can store any langual text when this language is supported by UTF8. Just use UTF8 encoding for your database. Regards Pavel Stehule see help for initdb and created

Re: [SQL] Average of Array?

t in the docs, and I'd like to make > sure I'm not missing something. it doesn't exists, but it is simple to develop it CREATE OR REPLACE FUNCTION array_avg(double precision[]) RETURNS double precision AS $$ SELECT avg(v) FROM unnest($1) g(v) $$ LANGUAGE sql; Regards Pavel Stehul

Re: [SQL]

Hello use a LO interface http://www.postgresql.org/docs/8.4/static/lo-interfaces.html exact form depends on language that you are use. Regards Pavel Stehule 2010/7/5 Trinath Somanchi : > Hi, > > I'm new in using BLOB. How will the insert for storing very large byte > stri

Re: [SQL]

heck it in testing environment with more than one user. The good size for text or bytea is less than 100M and real max isn't 2G but it is 1G. LO isn't these limits because it isn't accessable on SQL level. Regards Pavel Stehule > > -- > Sent via pgsql-sql mailing list (pgsq

Re: [SQL]

2010/7/5 silly sad : > On 07/05/10 10:43, Pavel Stehule wrote: > >> The good size for text or bytea is less than 100M and real max isn't >> 2G but it is 1G. LO isn't these limits because it isn't accessable on >> SQL level. > > any regular file on my

Re: [SQL]

2010/7/5 silly sad : > On 07/05/10 11:03, Pavel Stehule wrote: >> >> 2010/7/5 silly sad: >>> >>> On 07/05/10 10:43, Pavel Stehule wrote: >>> >>>> The good size for text or bytea is less than 100M and real max isn't >>>> 2G but

Re: [SQL]

2010/7/5 silly sad : > On 07/05/10 11:18, Pavel Stehule wrote: > >>> P.S. >>> Practically for storing pictures i prefer regular files. >>> >> >> how I say - it depends on application - sometime can be useful have to >> access to all data only

Re: [SQL] How would I store little key-Nr to text lists?

, you can use a hstore contrib module, but what you doing is best regards Pavel Stehule > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mail

Re: [SQL] plpgsql out parameter with select into

nteger as $$ begin _status := 10; end; $$ language plpgsql; create or replace function test3() returns void as $$ declare status integer; begin select into status _status from test1(); raise notice '%', status; end; $$ language plpgsql; this working for me. postgres=# select test3()

Re: [SQL] Boolean output representation.

' or its possible to change the output representation, > e.g., to "true" or "false" ("1", "0")? > no, it isn't possible - you can write a simple formating function or own custom data type. Regards Pavel Stehule > Regards, > Dmi

Re: [SQL] backup and document views and user functions

Hello 2010/8/30 David Harel : > Hi, > > I am looking for an easy way to backup views and functions. I want to store > them in our version control system. > move your functions and view to separate schema - and do backup with pg_dump -n schema regards Pavel Stehule > U

Re: [SQL] backup and document views and user functions

;ll see the query PG uses. > there is much more easy way to get a function source code SELECT pg_catalog.pg_get_functiondef(oid) Regards Pavel Stehule > ex. > production=# \df+ myschema.* > > * QUERY ** > SELECT n.nspname as "Schema", >   p.proname as

Re: [SQL] Is there a conditional string-concatenation ?

Hello more simply postgres=# CREATE OR REPLACE FUNCTION public.foo(a text, b text, c text) RETURNS text LANGUAGE sql AS $function$ SELECT coalesce($1 || $2 || $3, $1 || $2, $2 || $3) $function$ Regards Pavel Stehule 2010/10/12 Osvaldo Kussama : > 2010/10

Re: [SQL] insert into table from list or array ?

Hello 2010/10/18 Andreas : >  Hi, > > is it possible to insert into a table from list or an array ? > yes, it's possible INSERT INTO tmptab SELECT v FROM unnest(string_to_array('1,2,4,2,1',',')) g(v) Regards Pavel Stehule > Suppose there

Re: [SQL] Trailing spaces - is there an easier way?

| ''; ?column? -- (1 row) Time: 2.710 ms nic=# SELECT ''::char(6)::cstring || ''; ?column? (1 row) regards Pavel Stehule -- 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] Need help with plpgsql function.

SELECT 30,40; ELSE RETURN QUERY SELECT 60,70 UNION ALL SELECT 80,90; END IF; RETURN; END; $$ LANGUAGE plpgsql; SELECT * FROM foo(1); SELECT * FROM foo(2); Regards Pavel Stehule > > > > > > -- > View this message in context: > http://postgresql.1045698.n5.nab

Re: [SQL] Need help with plpgsql function.

2010/11/14 Adrian Klaver : > On Saturday 13 November 2010 11:15:51 pm Pavel Stehule wrote: > >> > } >> >> Hello >> >> you can use a RETURN QUERY statement - some like >> >> CREATE OR REPLACE FUNCTION foo(IN i int, OUT a int, OUT b int) >&g

Re: [SQL] error null value in column" concat_id" violates not-null constraint

cname(parameters) so if I would to fill table auxiliar from some function, then I'll use a statement INSERT INTO auxilar SELECT * FROM funcname(..) Regards Pavel Stehule > > I get "error null value in column"concat_id" violatres not null > constraint > > &g

Re: [SQL] conditional aggregates

Hello use a CASE statement http://www.postgresql.org/docs/7.4/static/functions-conditional.html Regards Pavel Stehule 2010/12/8 Marcin Krawczyk : > Hi list, > Can anyone advise me on creating an aggregate that would take additional > parameter as a condition ? For example, say I hav

Re: [SQL] How to convert string to integer

Hello you can use a ::int for converting to integer. Or better - you can alter column to integer. It will be faster and more correct. Regards Pavel Stehule 2010/12/15 venkat : > Dear All, >   How do i convert string to int > select SUM(pan_1) from customers1 where name='101

Re: [SQL] data import

Hello 2010/12/21 Viktor Bojović : > Hi, > > can anyone recommend me a windows and linux free tools for importing data > into postgre. > Source files are CSV or excel. PostgreSQL can read a CVS files via a COPY statement. You can use a \copy metacommand too from psql Regards

Re: [SQL] create function problem

Hello you badly use a IF statement. It's not C. Every IF must to finish with END IF this is IF .. THEN ELSEIF .. THEN .. ELSE END IF Regards Pavel Stehule 2010/12/30 Gary Stainburn : > Hi folks, > > I'm writing my first plpsql function in ages and I'm going blind tryi

Re: [SQL] aggregation of setof

Hello use a array constructor instead SELECT ARRAY(SELECT ...) Regards Pavel Stehule 2011/1/31 Andreas Gaab : > Functions apparently cannot take setof arguments. > > > > Postgres 8.4: > > > > CREATE OR REPLACE FUNCTION testtable(IN setof anyarray) > >

Re: [SQL] using of select (myfunction()).* is so slow

Hello If you use a record expansion over function's result, then function is called once for record's field. so don't do it on slow functions. Regards Pavel 2011/2/3 Gerardo Herzig : > Hi all, im using a function of my own in a subquery, and when wonderig > about the slowliness of this one, y

Re: [SQL] how control update rows

gt;    UPDATE "T" SET >        "C" = 1 >    WHERE "ID" > 100 >    RETURNING * > ) x > It's not implemented yet. You can use a stored procedure or temp tables instead. Regards Pavel Stehule > TIA, > Sabin > > > > -

Re: [SQL] using of select (myfunction()).* is so slow

>> > > M ok Thanks...So there is no workaround/alternative to this? > yes, (SELECT x.* from func(...) x) instead SELECT (func(...)).* regards Pavel Stehule > Gerardo > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to

Re: [SQL] UTF characters compromising data import.

rder to import  / use the data. > is impossible to import data without knowledge of encoding. you can use a some utils, that try to select a encoding http://linux.die.net/man/1/enca Regards Pavel Stehule > As always - thanks in advance for any help you might be able to provide. &g

Re: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1

Hello you can use a string%agg function if you have a 9.0. On older version there is a array_agg function select c1, array_to_string(array_agg(c2),',') from T1 group by c1 regards Pavel Stehule 2011/2/8 Emi Lu : > Good afternoon, > > Is there a method to retrieve the follow

Re: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1

> > array_to_string() + array() is exactly what I am looking for! > > I just wonder that array_to_string() + array() will provide me good > performance, right? If the calculation will be based on millions records. it depend on number of groups. This is correlated subquery - it must not be

Re: [SQL] Determine length of numeric field

Hello probably you have to use a explicit cast postgres=# select length(10::numeric::text); length 2 (1 row) Regards Pavel Stehule 2011/2/15 Tony Capobianco : > I'm altering datatypes in several tables from numeric to integer.  In > doing so, I get the following er

Re: [SQL] PL/SQL block error

Hello you should to wrap code to function or inline function everywhere. psql doesn't support PL/SQL and doesn't support inlined PL/SQL blocks. Regards Pavel Stehule 2011/2/16 Sivannarayanreddy > Hello, > I am very new to the postgres sql, i am trying to execute below p

Re: [SQL] Oracle Equivalent queries in Postgres

Hello PostgreSQL uses a different system tables than Oracle. Try to use a standardized information_schema instead - these views are same on PostgreSQL and Oracle. http://www.postgresql.org/docs/current/static/information-schema.html Regards Pavel Stehule 2011/2/16 Sivannarayanreddy

Re: [SQL] Oracle Equivalent queries in Postgres

gards Pavel Stehule 2011/2/16 Sivannarayanreddy > Hi Pavel, > In the given link, there are no views which can give information about > indexes. > > Is it possible for you to give me the equivalent queries in postgres? > > *Sivannarayanareddy Nusum** **| **System Analyst(Mone

Re: [SQL] Oracle Equivalent queries in Postgres

2011/2/16 Thomas Kellerer : > Pavel Stehule, 16.02.2011 11:50: >> >> Try to use a standardized information_schema instead - these views are >> same on PostgreSQL and Oracle. > > Unfortunately they are not the same: Oracle does not support > INFORMATION_SCHEMA >

Re: [SQL] Function compile error

/plpgsql.html Regards Pavel Stehule 2011/2/16 Sivannarayanreddy > Hello, > I am trying to create the function as below but it is throwing error > 'ERROR: syntax error at or near "DECLARE"', Could some one help me please > > CREATE FUNCTION check_password(databasen

Re: [SQL] Retrieve the column values of a record without knowing the names

Hello you can't simply iterate over record in plpgsql. You can use a some toolkits like PLToolkit, or different PL language like PLPerl, or some dirty trick http://okbob.blogspot.com/2010/12/iteration-over-record-in-plpgsql.html regards Pavel Stehule 2011/2/16 arthur_info : >

Re: [SQL] Obscure behavior of ORDER BY

his is correct, because spaces and white chars are ignored. Regards Pavel Stehule. 2011/3/21 Tambet Matiisen : > Hi everyone! > > I recently noticed obscure behavior of ORDER BY. Consider this example: > > select email from > ( > select '@'::text as email > union

Re: [SQL] update with recursive query

Hello it is possible in 9.1. In older version you have to use a temp table. Regards Pavel Stehule 2011/4/14 Steven Dahlin : > Is it possible to execute an update using recursion?  I need to update a set > of records and also update their children with the same value.  I tried the >

Re: [SQL] PLpgSQL variables persistance between several functions calls

Hello no, it's not possible Regards Pavel Stehule 2011/5/2 Charles N. Charotti : > Hello everybody ! > > I want to know if I could share PLpgSQL variables between different > functions and within different calls just using memory (not tables or other > methods). > >

Re: [SQL] FOR EACH STATEMENT trigger ?

support NEW or OLD tables in statement triggers. You should to use ROW triggers. Regards Pavel Stehule > Let me give you a real life example. > > Suppose we have the above table : > > CREATE TABLE T_PRODUIT_DISPO_PDD > (PRD_ID         INT         NOT NULL, >  PDD_BEGIN  

Re: [SQL] FOR EACH STATEMENT trigger ?

Hello it isn't bug. PostgreSQL doesn't support NEW and OLD tables like MSSQL does for statement triggers. Regards Pavel Stehule 2011/5/6 Frédéric BROUARD : > Hi there > > I am trying to get an example of SET BASED trigger logic with FOR EACH > STATEMENT, but I cannot find

Re: [SQL] problem with selecting from a function

> 7, (14, 'bla') > 7, (17, 'blu') > 7, (21, 'ble') <--- round brackets > This looks like an array but how can I split it up to columns or at least > extract the number-column? > this is composite value you can try SELECT object_id, (fctX(object_id

Re: [SQL] a strange order by behavior

Hello a equalent of C collate for UTF8 is ucs_basic Regards Pavel Stehule 2011/6/22 Samuel Gendler : > > > On Tue, Jun 21, 2011 at 3:37 AM, Eyal Wilde wrote: >> >> the database collation is: en_US.UTF-8 >> drop table t1; >> create table t1 (recid int ,f1 varch

Re: [SQL] a strange order by behavior

Hello Peter > Pavel suggested using a collation of ucs_basic, but I get an error when I > try that on linux: > $ createdb -U u1 --lc-collate=ucs_basic -E UTF-8 test > createdb: database creation failed: ERROR:  invalid locale name ucs_basic isn't this a bug in collations? Regards Pavel > I wa

Re: [SQL] a strange order by behavior

rád Syn Záruba Šebíšek (6 rows) postgres=# select * from x order by a collate "cs_CZ"; a --- Crha Chromečka Semerád Syn Šebíšek Záruba (6 rows) Regards Pavel Stehule -- 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] a strange order by behavior

2011/6/22 Peter Eisentraut : > On ons, 2011-06-22 at 02:39 -0700, Samuel Gendler wrote: >> Pavel suggested using a collation of ucs_basic, but I get an error >> when I >> try that on linux: >> >> $ createdb -U u1 --lc-collate=ucs_basic -E UTF-8 test >> createdb: database creation failed: ERROR:  in

Re: [SQL] a strange order by behavior

2011/6/23 Peter Eisentraut : > On tor, 2011-06-23 at 05:57 +0200, Pavel Stehule wrote: >> 2011/6/22 Peter Eisentraut : >> > On ons, 2011-06-22 at 02:39 -0700, Samuel Gendler wrote: >> >> Pavel suggested using a collation of ucs_basic, but I get an error >&g

Re: [SQL] best performance for simple dml

Hello try it and you will see. Depends on network speed, hw speed. But the most fast is using a COPY API http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html Regards Pavel Stehule 2011/6/27 chester c young > what is the best performance / best practices for frequently-used sim

Re: [SQL] best performance for simple dml

) exec("insert into foo values($1), itoa(i)); exec('commit'); Regards Pavel Stehule > > --- On Mon, 6/27/11, Pavel Stehule wrote: > > From: Pavel Stehule > Subject: Re: [SQL] best performance for simple dml > To: "chester c young" > Cc: pgsql-sql@po

Re: [SQL] best performance for simple dml

t remove a connection cost, but nothing more. You can use a more connections to do paralel inserts - it has a sense. look on pgpool or other similar sw for connection pooling Pavel > > --- On *Mon, 6/27/11, Pavel Stehule * wrote: > > > From: Pavel Stehule > Subject: Re:

Re: [SQL] Usage of function retruning record in query

>> > Hmm.. no takers? I guess not possible then? > Thanks anyway > hello try to wrap your query to subselect, npcps_201=# select 1,2,2,3; ?column? │ ?column? │ ?column? │ ?column? ──┼──┼──┼── 1 │2 │2 │3 (1 row) Ti

Re: [SQL] overload

Hello using a "window" implemented via LIMIT OFFSET is not good - it is solution on some systems where cursors are not available, but it is bad solution on PostgreSQL. Use a cursor instead - it is significantly more efective with less memory requests. Regards Pavel Stehule 2011/

Re: [SQL] using explain output within pgsql

> database. > Now I'd like to store besides the results the dependencies to the tables > which were used to generate the report. with this information i could > invalidate cache results for the tables I'm going to import with my etl Hello try FOR l_explain IN EXPLAIN

  1   2   3   >