2011/2/5 Adam PAPAI :
> Pavel Stehule wrote:
>> Hello
>>
>> You should to initialize database with correct locale. You can't to
>> change locale after database is created.
>>
>> /usr/local/pgsql91/bin/createdb test --locale=cs_CZ.utf-8 -e UTF-8
>
or
when you has more hw
Depends on application and size of data - if you has lot of logs, or
some like OLAP data, then is very practical use more than one
database.
Regards
Pavel Stehule
> regards
> thomas
>
>
> Am 08.02.2011 09:30, schrieb Szymon Guz:
>>
>> Hi,
>>
ORDER BY foo.' || quote_ident("desc") || ' DESC'
Regards
Pavel Stehule
2011/2/17 Jeremy Palmer :
> Hi,
>
> I'm creating a pl/pgSQL function that returns a table that has a column name
> which is the same as a PostgreSQL reserved. In the below example a have
se, send a execute plans - see statement EXPLAIN
Regards
Pavel Stehule
2011/2/21 Joel Reed :
> Hoping someone will be kind enough to share how to write a query that uses
> 9.0's string_agg with a subselect, something like...
>
> select m.id,m.subject,m.from_address,m.date_sen
; I'm forced to use v7.4.
>
> If this is a known error, can I (with the version ; ^) ) get around it?
>
> Ultimately I need to FOR..LOOP through through records and the table and
> cols will change. Any suggestions???
>
use a EXECUTE statement and FOR IN EXECUTE statement
rega
er;
> y integer;
> BEGIN
> y := somecalculation;
> x := 'callback'(y); --This is what I need
EXECUTE 'SELECT ' || callback || '($1)' USING y INTO x;
there are no other way than EXECUTE
attention - there is a sql injection risk
Hello
there isn't a simple way :(
the most simply way is using string_to_array function
SELECT func(string_to_array('1,2,3,4,5',','));
Regards
Pavel Stehule
2011/3/5 Andre Lopes :
> Hi,
>
> I need to transform an PHP array to an PlPgSQL array. The PH
ocs/9.0/static/hstore.html
attention - it doesn't allow a nested values
Regards
Pavel Stehule
for more complex values is other was - using a temp tables - you can
fill a temp table and in next step a plpgsql code use this temp table.
But it should have a performance impacts.
> Best
2011/3/5 Dmitriy Igrishin :
>
>
> 2011/3/5 Pavel Stehule
>>
>> 2011/3/5 Andre Lopes :
>> > Hi Pavel,
>> >
>> > Thanks for the reply.
>> >
>> > In PlpgSQL there is possible to define arrays with "Key => Value, Key =>
>
2011/3/5 Dmitriy Igrishin :
>
>
> 2011/3/5 Pavel Stehule
>>
>> 2011/3/5 Dmitriy Igrishin :
>> >
>> >
>> > 2011/3/5 Pavel Stehule
>> >>
>> >> 2011/3/5 Andre Lopes :
>> >> > Hi Pavel,
>> >> >
Hello
look on array_lower and array_upper functions
http://www.postgresql.org/docs/8.2/static/functions-array.html
for one dimensional arrays - select array_upper(var,1) - array_lower(var,1)
Regards
Pavel Stehule
2011/3/6 Andre Lopes :
> Hi,
>
> Just another question about thi
Hello
2011/3/9 abcdef :
> I use postgresql in red hat linux .
> I want to know how I can find out all the procedure stored in the database .
> Any SQL command to do it ???
>
you can try \df in psql or
select * from pg_proc
Regards
Pavel Stehule
>
> --
> View this messag
2011/3/9 Dmitriy Igrishin :
> Hey all,
>
> dmitigr=> select to_char(1, '9');
> to_char
> -
> 1
>
> dmitigr=> select length(to_char(1, '9'));
> length
>
> 2
>
> Why to_char() includes preceding blank space in the result ?
it is compatibility with Oracle?
Regards
Pavel
2011/3/9 Dmitriy Igrishin :
>
>
> 2011/3/9 Pavel Stehule
>>
>> 2011/3/9 Dmitriy Igrishin :
>> > Hey all,
>> >
>> > dmitigr=> select to_char(1, '9');
>> > to_char
>> > -
>> > 1
>> >
>
e oralce to fit my
> requirement ???
> So, the whole table can be view within the screen of psql ???
>
try wrap mode
http://archives.postgresql.org/pgsql-committers/2008-05/msg00126.php
you can try
http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#psql_together_with_less
gt;
these outputs are not too helpful - send a EXPLAIN ANALYZE result
you can use a small aplication for storing plans http://explain.depesz.com/
regards
Pavel Stehule
>
> *
> The beginning of the 8.4:
> *
> &
ression
to_char(course_begin_date, 'MMDD'::text) = '20101025'::text
should be a problem
much better is test on equality in date domain like:
course_begin_date = to_date('20101025', 'MMDD')
this is faster and probably better estimated
Regards
Pav
.4.
> Thanks much.
>
>
ok, sorry, do column_course_begin::date = ...
:)
Pavel
>
>
> -Original Message-
> From: Tomas Vondra [mailto:t...@fuzzy.cz]
> Sent: Wednesday, March 16, 2011 4:40 PM
> To: Pavel Stehule
> Cc: pgsql-general@postgresql.org; Davenport, J
?
No, it's not supported :(
Regards
Pavel Stehule
>
> Best Regards
> Dan S
>
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
E id = $1;
> $$;
>
>
you can pass a table name as parameter only:
CREATE FUNCTION foo(tablename text)
RETURNS SETOF text AS $$
BEGIN
RETURN QUERY EXECUTE 'SELECT content FROM ' || quote_ident(tablename);
END;
$$ LANGUAGE plpgsql;
Regards
Pavel Stehule
>
>
>
>
sql;
it can work too, but there is sql injection risk.
Do newer 'SELECT ... FROM ' || tabname || ' ...
Regards
Pavel Stehule
>
>
> Thanks & Regards,
> Vibhor Kumar
> EnterpriseDB Corporation
> The Enterprise PostgreSQL Company
> vibhor.ku...@en
2011/3/21 Vibhor Kumar :
>
> On Mar 22, 2011, at 1:32 AM, Pavel Stehule wrote:
>
>> it can work too, but there is sql injection risk.
>>
>> Do newer 'SELECT ... FROM ' || tabname || ' ...
>>
>> Regards
>>
>> Pavel Stehule
>
>
2011/3/21 Vibhor Kumar :
>
> On Mar 22, 2011, at 1:52 AM, Pavel Stehule wrote:
>
>> simply thinks as using USAGE clause or functions quote_ident,
>> quote_literal are faster and absolutly secure :). Software like SQL
>
> I don't think usage of quote_ident in cu
ey bigint;
> begin
> execute 'select NEW.esid' into pkey;
> end;
> $$ language plpgsql
>
> Obviously this particular code could be re-written as a simple assignment
> but I need the
> "esid" part to be dynamic. Is this possible?
yes, it's possibl
Hello
SPI_finish try to release resources and memory. Maybe you has broken memory.
Try to compile postgres with --enable-debug and --enable-cassert
flags. You can take more info from core dump.
Regards
Pavel Stehule
2011/3/23 Jorge Arévalo :
> Hello,
>
> I'm writing my own
DatumGetTextP(DirectFunctionCall1(textin,
CStringGetDatum(buf.data)));
PG_RETURN_TEXT_P(result);
}
CREATE OR REPLACE FUNCTION quote_literal(anyelement)
RETURNS text
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION quote_literal(text)
RETURNS text
AS 'MODULE_PATH
l
> HTH,
> WBL
you can't prepare a DO statement - it has no plan :(. I worked on
parametrized DO statement, but this patch was rejected as premature
optimalization.
Regards
Pavel Stehule
>>
>>
>>
>>
>>
>>
>>
>> --
>> Sent via pgsql-
r IN EXECUTE 'SELECT .. FROM ' || quote_ident(tableName) || ' '
LOOP
...
you can use OPEN FOR EXECUTE too, but FOR statement is preferable
Regards
Pavel Stehule
http://www.postgresql.org/docs/9.0/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-OPENING
> then I creat
Hello
2011/3/28 Terry Kop :
> I'm trying to create a function that will take setof results from various
> other functions (they all produce the same output format). Is this possible?
> if so how do call it.
No, this isn't possible.
Regards
Pavel Stehule
>
> ex.
>
functions, you have to register new type
look to postgresql sources - contrib, there is very simple type citext
http://doxygen.postgresql.org/dir_23dd6926f287bddac2c9dcb7db5b1712.html
http://doxygen.postgresql.org/citext_8c-source.html
Any contrib module has sql file with registrations
Regards
TE FUNCTION do_stuff ...
> BEGIN
> CASE cond
> WHEN 'a' THEN func := f1;
> WHEN 'b' THEN func := f2;
> WHEN 'c' THEN func := f3;
> END CASE
>
> SELECT * FROM foobar WHERE func (foobar.age);
>
y(select unnest(array[25,26,27]::oid[])::regtype);
?column?
{text,oid,tid}
(1 row)
Regards
Pavel Stehule
>
> Regards
>
> Best Regard
>
> Eng. Salah Al Jubeh
> Palestine Polytechnic University
> College of Applied Science
> Computer Science
&
2011/4/8 Merlin Moncure :
> On Fri, Apr 8, 2011 at 5:57 AM, gmb wrote:
>> Hi
>>
>> Is it possible to do a JOIN between a VIEW and the output of a FUNCTION?
>
> yes.
yes, it is possible. Just I am not sure if original query wasn't
directed to >>lateral<< feature.
Pavel
>
> merlin
>
> --
> Sent
ROM STDIN" with the
> native C API?
look to source code on \copy implementation in psql
http://doxygen.postgresql.org/bin_2psql_2copy_8c.html
http://www.postgresql.org/docs/8.1/static/libpq-copy.html
Regards
Pavel Stehule
> I found some documentation, however as I am no C na
Hello
2014-05-27 20:30 GMT+02:00 Paul Jones :
> I have written a user-defined type that allows direct import and printing
> of
> DB2 timestamps.It does correctly import and export DB2 timestamps,
> butI'm wondering ifsomeone could tell me if I made anymistakes in
> the C code, particularly w.r.t
2014-05-29 18:04 GMT+02:00 Paul Jones :
> On Wed, May 28, 2014 at 10:51:43AM +0200, Pavel Stehule wrote:
> >
> > Hello
> >
> >
> > 2014-05-27 20:30 GMT+02:00 Paul Jones :
> >
> > > I have written a user-defined type that allows direct import and
Hello
You are using PLpgSQL CASE statement
this start by CASE keyword and finishing by END CASE keywords
CREATE OR REPLACE FUNCTION sn_dm_b.pm_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
CASE
WHEN NEW.period = 201001
THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
2014-06-26 18:28 GMT+02:00 Shaun Thomas :
> On 06/25/2014 05:19 PM, Dennis Ryan wrote:
>
> CASE
>> WHEN NEW.period = 201001
>> THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
>> END;
>>
>
> You can't just have a bare CASE statement in plpgsql. Try this:
>
>
> CREATE OR REPL
2014-06-26 18:26 GMT+02:00 Raymond O'Donnell :
> On 25/06/2014 23:19, Dennis Ryan wrote:
> > I having trouble with correct syntax to get this trigger function to
> > compile. I have tried every combination of removing the ‘;’ characters
> > but the function will not compile. Can someone tell me
Hello
plpgsql_check is PostgreSQL extension for deep check of plpgsql functions.
Only PostgreSQL 9.3 was supported.
Today I did a backport for PostgreSQL 9.2
https://github.com/okbob/plpgsql_check
Regards
Pavel Stehule
>
> psql -f myfile.sql
>
yes, it is good way
stored procedures are code as any other and there are same rules. Use
files, use a your preferred editor, use makefiles, use GIT
Regards
Pavel Stehule
>
> --
> Jacob
>
>
>
> --
> Sent via pgsql-general mailing list
Hello
I found a interesting extension
http://code.malloclabs.com/pg_schema_triggers
Regards
Pavel
2014-07-25 20:01 GMT+02:00 Alvaro Herrera :
> Vasudevan, Ramya wrote:
> > >> You could compare list of tables before (_start) and after (_end) the
> ddl. Doing it in plpgsql will be tricky, but i
Hello
2014-07-25 20:46 GMT+02:00 Alvaro Herrera :
> Pavel Stehule wrote:
> > Hello
> >
> > I found a interesting extension
> > http://code.malloclabs.com/pg_schema_triggers
>
> Ah, I remember that. I find that approach more cumbersome to use than
> mine.
: 1.698 ms
world=# SELECT * FROM xx(2);
a | b
---+---
|
(1 row)
Regards
Pavel Stehule
2014-07-30 20:13 GMT+02:00 Seref Arikan :
> Greetings,
> I want to call a function using a column of a table as the parameter and
> return the parameter and function results together.
> Th
Hi
2014-08-14 8:10 GMT+02:00 Patrick Dung :
> Thanks all for the help.
>
> BTW, letter casing is just a preference.
> Some people liked to use all small caps, some liked to use all big caps.
> I sometimes found that mixed case is more meaningful for the filed (eg.
> serialnumber vs serialNumber)
Hi
2014-08-22 9:05 GMT+02:00 David G Johnston :
> Piotr Gasidło wrote
> > Hello,
> >
> > I found strange PostgreSQL 9.3 behavior:
> >
> >> select now()::timestamp, 'now()'::timestamp;
> > now | timestamp
> > +---
Hi
psql variables has different format and its is not directly related to psql
options. But you can do:
[pavel@localhost ~]$ psql postgres --set=myproname=upper
psql (9.5devel)
Type "help" for help.
postgres=# select proname, prosrc from pg_proc where proname = :'myproname';
proname | prosrc
Hi
you can define || operator for char(N) type
postgres=# select oprname, oprleft::regtype, oprright::regtype from
pg_operator where oprname = '||'
;
oprname | oprleft | oprright
-+-+-
|| | bytea | bytea
|| | text| text
|| | te
2014-09-03 15:25 GMT+02:00 Szymon Guz :
>
>
>
> On 3 September 2014 15:20, Pavel Stehule wrote:
>
>> Hi
>>
>> you can define || operator for char(N) type
>>
>> postgres=# select oprname, oprleft::regtype, oprright::regtype from
>> pg_operato
2014-09-03 16:01 GMT+02:00 Kevin Grittner :
> Pavel Stehule wrote:
> > 2014-09-03 15:25 GMT+02:00 Szymon Guz :
>
> >> I think we should have this in core, as this definitely is a bug.
> >
> > hard to say - anything about CHAR(N) is strange,
>
> On a quick
2014-09-04 6:27 GMT+02:00 Vinayak :
> Hello Pavel,
>
> Thank you for reply.
> >postgres=# select 'abc '::char(7) || 'dbe '::char(6);
> >?column?
> >
> > *abcabc*
> >(1 row)
> but it gives the result "abcabc". It should be "abcdbe".
>
>
yes
create or replace f
2014-09-04 11:13 GMT+02:00 Vinayak :
> Hi,
>
> The || operator with arguments (character,character) works fine and even ||
> operator(character,varchar) also works fine.
> but || operator is not working as expected with arguments character data
> type and any other data type like integer,smallint,
2014-09-15 19:34 GMT+02:00 cowwoc :
> On 15/09/2014 7:03 AM, Chris Travers wrote:
>
> I have a few questions on this, the answers of which may help answer your
> question:
>
> 1. How well does having a server-side JVM work, resource-wise, when you
> have a forked process model like PostgreSQL?
2014-09-15 19:37 GMT+02:00 cowwoc :
> On 15/09/2014 7:58 AM, Bill Moran wrote:
>
>> On Sun, 14 Sep 2014 22:22:21 -0700 (PDT)
>> cowwoc wrote:
>>
>>> Out of curiosity, why is Postgresql's Java support so poor?
>>>
>> To trampoline off what others have said: it gets implemented and
>> maintained if
2014-09-15 19:46 GMT+02:00 Pavel Stehule :
>
>
> 2014-09-15 19:37 GMT+02:00 cowwoc :
>
>> On 15/09/2014 7:58 AM, Bill Moran wrote:
>>
>>> On Sun, 14 Sep 2014 22:22:21 -0700 (PDT)
>>> cowwoc wrote:
>>>
>>>> Out of curiosity, why i
2014-09-15 19:49 GMT+02:00 cowwoc :
> Hi Pavel,
>
> On 15/09/2014 1:40 PM, Pavel Stehule wrote:
>
> The main drivers are:
>
>>
>>1. Not having to learn yet another language. I find the
>>expressiveness and readability of the other scripting languag
2014-09-16 17:39 GMT+02:00 Kevin Grittner :
> Abelard Hoffman wrote:
>
> > I have a user-defined GUC variable that was set at the db level. e.g.,
> >
> > ALTER DATABASE mydb SET myapp.user_id TO '1'
> >
> > Works fine. When I do a pg_dump, however, that variable isn't included.
> > Is that expe
2014-09-17 22:54 GMT+02:00 Peter Eisentraut :
> On 9/15/14 1:46 PM, Pavel Stehule wrote:
> > I am strong sceptic. There is relative slow progress in JDBC driver,
> > that is 100x more important project than PL/Java - so It is hard to
> > believe, so there can be 3 developers,
Hi
2014-09-25 12:24 GMT+02:00 Emanuel Araújo :
> Thank's Adrian,
>
> I want really create another CURRENT_DATE called SYSDATE.
>
It needs a hack to postgres. Pseudoconstant functions needs a support in
PostgreSQL parser. There is no other possibility
Pavel
>
> postgres=# SELECT CURRENT_DATE ;
2014-09-28 20:30 GMT+02:00 Gerardo Herzig :
> Hi all. I see an entire database, with all the stored procedures writen in
> plpgsql. Off course, many (if not all) of that SP are simple inserts,
> updates, selects and so on.
>
> So, i want to test and show the differences between doing the same
> fu
2014-09-28 21:29 GMT+02:00 Gerardo Herzig :
> > Hi all. I see an entire database, with all the stored procedures
> > writen in plpgsql. Off course, many (if not all) of that SP are
> > simple inserts, updates, selects and so on.
> >
> > So, i want to test and show the differences between doing the
ension_name;
Regards
Pavel Stehule
>
>
>
> With Regards
> M N Muralikrishna
>
>
>
Hi
try to use a Perl implementation https://github.com/davidfetter/DBI-Link
There is a DBD driver for DB2 http://search.cpan.org/dist/DBD-DB2/DB2.pod
Or you can use a mentioned fdw wrapper - there is ODBC wrapper
https://wiki.postgresql.org/wiki/Foreign_data_wrappers#odbc_fdw and you can
access
erlin and Pavel...I will give it a try.
>>
>> On Wed, Oct 22, 2014 at 6:36 AM, Merlin Moncure > <https://e.mail.ru/compose/?mailto=mailto%3ammonc...@gmail.com>> wrote:
>>
>> On Wed, Oct 22, 2014 at 5:56 AM, Pavel Stehule > <https://e.mail.ru/compose
S
or more exactly:
postgres=# set datestyle to SQL,DMY;
SET
postgres=# SELECT '19/08/2014'::date;
date
19/08/2014
(1 row)
http://www.postgresql.org/docs/9.4/static/datatype-datetime.html
Regards
Pavel Stehule
2014-12-28 11:43 GMT+01:00 Arup Rakshit :
> Hi,
>
>
2014-12-28 12:06 GMT+01:00 Arup Rakshit :
> On Sunday, December 28, 2014 12:54:30 PM Pavel Stehule wrote:
> > Hi
> >
> > try
> >
> > postgres=# set datestyle to DMY;
> > SET
> > postgres=# SELECT '19/08/2014'::date;
> > date
Hi
it should to work for JSON too
http://8kb.co.uk/blog/2015/01/16/wanting-for-a-hstore-style-delete-operator-in-jsonb/
Regards
Pavel
2015-01-22 20:37 GMT+01:00 Wells Oliver :
> Hey all. I have a trigger function which does a delta of two hstore values
> just doing a - b; this check is perform
Hi
2015-01-23 20:56 GMT+01:00 Suresh Raja :
> Hi All:
>
> We are running a function with a loop in it. We tried
> commit;
> We are getting error with above command. How can i easily commit withing
> a function.
>
>
It is not possible in PostgreSQL
Regards
Pavel Stehule
>
> Thanks,
> -SR
>
postgres 9.4?
>
This limit was removed. You can use any session vars with patter
"someprefix.somevar" without necessity to enable class "someprefix"
Regards
Pavel Stehule
>
> Thank you
>
>
>
> --
><http://www.avast
Hi
you can use a lo_* (lo_open, lo_close, ...) functions called via
FirectFunctionCall
http://michael.otacoo.com/postgresql-2/playing-with-large-objects-in-postgres/
http://www.postgresql.org/docs/9.3/static/lo-interfaces.html
Regards
Pavel Stehule
2015-01-26 20:19 GMT+01:00 Spiros Ioannou
Hi
you can use a advisory locks for singleton implementation
http://www.postgresql.org/docs/9.4/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
Regards
Pavel
2015-01-26 20:58 GMT+01:00 Suresh Raja :
> Before running a running a function, i would check only 1 instance is
> running. If al
2015-01-26 20:54 GMT+01:00 Spiros Ioannou :
> But these need a connection to the postgresql backend. It seems an
> overkill for postgresql to connect to itself, I would prefer something more
> optimal. It seems for now that reading libpq's source code is the only way
> to go.
>
No, it doesn't ne
Hi
PostgreSQL doesn't have a dbms_scheduler. If you need it, you can use a
EnterpriseDB - commercial fork with Oracle migration tools. There it is.
You can use a scheduler pgAgent - https://github.com/postgres/pgagent
http://www.pgadmin.org/docs/dev/pgagent.html
Regards
Pavel
2015-02-03 14:16
--create or replace type suborder_list_table as table of suborder_list;
> this on *oracle *formate
>
This syntax is not supported in Pg - resp. a collections are not supported
by PostgreSQL.
use a arrays instead
DECLARE array_var order_list[];
http://www.postgresql.org/docs/9.4/static/arrays.html
Regards
Hi
It is currently impossible on unpatched postgres.
I am selling a patch to postgres that does a obfuscation of procedure body
Regards
Pavel Stehule
2015-02-11 10:54 GMT+01:00 Saimon Lim :
> Hi
> I want to hide my own stored procedures' bodies from the specific user.
> As
Hi
you can call "input function" - jsonb_in
Jsonb *targetjsonbvar = DatumGetJsonb(DirectFunctionCall1(json_in,
CStringGetDatum(cstrvalue)));
Regards
Pavel Stehule
2015-02-13 11:32 GMT+01:00 Igor Stassiy :
> Hi,
>
> Is there a way to initialize JsonbValue from cstring f
2015-02-13 14:13 GMT+01:00 Ramesh T :
> COLLECT
Hi
Depends on what you needs. The collections are not supported by PostgreSQL
- use a arrays instead.
http://www.postgresql.org/docs/9.4/static/arrays.html
Regards
Pavel Stehule
2015-02-14 17:22 GMT+01:00 Ramesh T :
> dbms_scheduler.create_job(
> job_name => 'DELETE_EMPTY_PART_NUMS'
> ,job_type => 'PLSQL_BLOCK')
>
> without pgagent or cron is not possible..?
>
Not in PostgreSQL
Regards
Pavel Stehule
>
&
27;t think so it is necessary in this moment (so I am against a
introduction new prefix)
a) PostgreSQL safely solves conflicts between plpgsql and SQL - what
Oracle doesn't
b) Usual prefix for plpgsql variables is "_" - I don't see a difference
between @,?,:
Regards
Pavel Stehule
Hi
It is little bit strange, it works for me
postgres=# create temp table foo(a int);
CREATE TABLE
postgres=# do $$ begin insert into foo values(10); end $$; -- plpgsql
DO
postgres=# select * from foo;
a
10
(1 row)
Regards
Pavel
2015-02-20 9:07 GMT+01:00 Vincenzo Romano :
> PL/PgSQL
Hi
2015-02-19 21:42 GMT+01:00 Juan Pablo L. :
> Hello, i have created a function (in C) that receives an array that
> contains tuples of ID's and values.
>
> The function is to execute updates on each ID assigning the value, but if
> one of these operation fails (does not meet certain criteria)
>
> "people";
> non_x_loc | x_loc
> ---+---
> 1 | 2
> (1 row)
>
> This *FILTER* method is available from 9.4, How can I get the same output
> below 9.4 version ?
>
>
use SQL CASE
SELECT COUNT(CASE lower(location) &
Some other solutions
http://postgres.cz/wiki/PostgreSQL_SQL_Tricks_II#Fast_searching_of_longer_prefix
2015-02-25 9:04 GMT+01:00 Tim Smith :
> Will take a look. Thanks steve.
>
> On 24 February 2015 at 23:57, Steve Atkins wrote:
> >
> > On Feb 24, 2015, at 3:50 PM, Tim Smith
> wrote:
> >
> >>
xpression to perform the conversion.
postgres=# alter table foo alter column a type timestamp using a::timestamp;
ALTER TABLE
postgres=#
Regards
Pavel Stehule
(array[1,2,3,4]);
unnest │ unnest
┼
1 │ 1
2 │ 2
1 │ 3
2 │ 4
(4 rows)
so result is - don't use SRF (set returning funtion) in column list
if you don't need.
9.3 will support LATERAL clause, and I hope so we can drop this
f
Hello
>>
>> Once more quickie. It seems that I am going to be asked for my password
>> every time psql loops through the copy statement.
>>
>> What is considered best practices to handle authentication? I am
>> connecting locally, as myself as the user and I'm being asked for my user
>> password.
rigger).
-1
building dynamic used array, that should or should not used can
significantly decrease performance :(
if you need it, you can use workaround - session variables.
Regards
Pavel Stehule
>
> Would help debug database triggers that have cascaded events.
>
> Regards,
>
>
Hello
2013/1/18 Robert James :
> I'd like to understand better why manually using a temp table can
> improve performance so much.
one possible effect - there should be different statistic
did you look on EXPLAIN ANALYZE?
Regards
Pavel Stehule
>
> I had one complicated que
lf). How do I do this?
use log_min_duration_statement = 0
Regards
Pavel Stehule
>
> Thanks,
>
> Matthew
>
> --
> Matthew Vernon
> Quantitative Veterinary Epidemiologist
> Epidemiology Research Unit, SAC Inverness
>
>
> --
> Sent via pgsql-general mailing list (pgsql-gen
Hello
what did you do exactly?
please, can you send more details?
https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
Regards
Pavel Stehule
2013/1/25 Electric Boy :
> I have a problem with posgresql 8.4, yesterday when I tried to reinstall and
> do restore the old database no
join where result is related to some function result can be very
slow, because estimation will be out and any repeated function
evaluation is just expensive.
You can try use a functional index.
create index on tab2 ((substring(tab1.code from 1 for 5))
Regards
Pavel Stehule
>>
>> R
2013/1/31 Vincent Veyron :
> Le jeudi 31 janvier 2013 à 09:49 +0100, Pavel Stehule a écrit :
>
>> any join where result is related to some function result can be very
>> slow, because estimation will be out and any repeated function
>> evaluation is just expensive.
>&g
2013/1/31 Vincent Veyron :
> Le jeudi 31 janvier 2013 à 11:06 +0100, Pavel Stehule a écrit :
>> 2013/1/31 Vincent Veyron :
>> >
>> > Suppose I have a query of the form
>> >
>> > SELECT my_function(column_1), column_2
>> > FROM my_table
>>
Hello
2013/1/31 :
>
> Pavel Stehlule wrote:
>
>>> >> Hi,
>>> >>
>>> >> I am trying to match items from 2 tables based on a common string.
>>> >> One is a big table which has one column with entries like XY123, ABC44, =
>>> etc
>>> >> The table has an index on that column.
>>> >> The second table
2013/2/2 :
> Pavel Stehule wrote:
>
>>>
>>> but maybe https://github.com/dimitri/prefix can help
>>>
>
> Hi Pavel,
>
> thanks - this works perfect. However, it does not seem to play well
> with the optimizer, so I ended up with
>
> select
2013/2/2 Pavel Stehule :
> 2013/2/2 :
>> Pavel Stehule wrote:
>>
>>>>
>>>> but maybe https://github.com/dimitri/prefix can help
>>>>
>>
>> Hi Pavel,
>>
>> thanks - this works perfect. However, it does not seem to play
Hello
2013/2/3 Edson Richter :
> Hi!
>
> Assumptions: PostgreSQL 9.2.2, Win64, already run vacumm full freeze
> analyze. No problems in the database. I know there are 1247 records to be
> found.
> Why does these queries return different results:
>
>
> select count(*) from parcela
> where id not in
to the audit table (meaning that any logging is
> going to have to occur via a function with SECURITY DEFINER).
>
> Any advice or "you don't want to it that way" abuse would be much
> appreciated.
Moving and too "smart" logic to triggers is usually bad idea
2013/2/5 Bruce Momjian :
> On Mon, Feb 4, 2013 at 08:33:02AM -0600, Merlin Moncure wrote:
>> On Sat, Feb 2, 2013 at 11:36 AM, Carlo Stonebanks
>> wrote:
>> > Here is an advantage Plpgsql has:
>> > http://www.postgresql.org/docs/9.1/static/plpgsql-expressions.html
>> >
>> > I guess you can offset
e a way to make the
> "explain analyze" output more verbose?
>
> Or do I have to run each of procedure's
> statements by hand, preprending them
> with "explain analyze"?
>
no, it is not possible
http://blog.guillaume.lelarge.info/index.php/post/2012/03/31/Prof
401 - 500 of 1346 matches
Mail list logo
