if (! defined $patterns{$patt}) {
> > > $patterns{$patt}=1;
> > > }else{
> > > $patterns{$patt}++;
> > > }
> > > }
> > > }
> > > foreach $patt (keys %patterns){
> > > my $sql="insert into patterns
> > values('".$patt."',".$patterns{$patt}.")";
> > > spi_exec_query($sql);
> > > }
> > > return '';
> > > $BODY$
> > > LANGUAGE plperl VOLATILE
> > > COST 100;
> > >
> > >
> > >
> > > --
> > > ---
> > > Viktor Bojovi??
> > > ---
> > > Wherever I go, Murphy goes with me
> >
>
>
>
> --
> ---
> Viktor Bojovi??
> ---
> Wherever I go, Murphy goes with me
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
gt; }
> foreach $patt (keys %patterns){
> my $sql="insert into patterns values('".$patt."',".$patterns{$patt}.")";
> spi_exec_query($sql);
> }
> return '';
> $BODY$
> LANGUAGE plperl VOLATILE
> COST 100;
>
&
5 | 1.55 | 45.00| 1.23 | 23.25
> > 6 | 3.60 | 69.00| 2.98 | 62.66
> > How will I manage unique column names for this output?
> >
> > --
> > View this message in context:
> > http://postgresql.1045698.n5.nabble.com/Usage-of-fu
ll I manage unique column names for this output?
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Usage-of-function-retruning-record-in-query-tp4549140p4550092.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
> --
>
---
> > 0 20:00
> > 1 20:03
> > 1 20:04
> > 0 20:05
> > 1 20:05
> > 0 20:08
> > 1 20:09
> > 0 20:10
> >
> > I'd want the select to return:
> >
> > ID TS (HH:MM)
> > ---
> &
I'd want the select to return:
> >
> > ID TS (HH:MM)
> > ---
> > 0 20:00
> > 0 20:05
> > 0 20:10
> > 1 20:03
> > 1 20:09
> >
> >
> > Does my question make sense?
> >
> > Thanks in a
SELECT *
> FROM Yourtable AS B
> WHERE B.id = A.id
>AND B.ts > A.ts - INTERVAL '5 MINUTES'
>AND B.tx < A.ts )
>
> ORDER BY id, ts;
>
> --
> Regards,
> Richard Broersma Jr.
--
Se
5) is included for the
opposite reason.
Let me restate my requirement again with a little more detail. I want to
select records grouped by ID, ordered by timestamp, in ascending order so I'm
starting with the oldest, that are at least X minutes apart.
I hope that helps.
Thanks again,
Wayne
; > 0 20:00
> > 1 20:03
> > 1 20:04
> > 0 20:05
> > 1 20:05
> > 0 20:08
> > 1 20:09
> > 0 20:10
> >
> > I'd want the select to return:
> >
> > ID TS (HH:MM)
> > --
want the select to return:
ID TS (HH:MM)
---
0 20:00
0 20:05
0 20:10
1 20:03
1 20:09
Does my question make sense?
Thanks in advance,
Wayne
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
An: pgsql-sql@postgresql.org
Betreff: Re: [SQL] returning an array as a list fo single-column rows?
The following will return the elements of an array each in its
Own row. Using both array_lower() and array_upper() the number of array
Elements and their internal index may vary from record to
Hi,
I realize I probably lost my marbles but I've been having a god
awful time with a single query:
control:
controller_id pk;
datapack:
controller_id fk;
I need to get all entries from the table contr
this should work,
#
SELECT date_trunc('day',endtime),count(*)
FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01'
GROUP BY 1
ORDER BY 1;
hope this helps
best regards,
Stefan
Am Donnerstag, 16. März 2006 06:18 schrieb Bryce Nesbitt:
> I've got a working query:
>
Is there a way to execute an external i.e. system command from inside a
pl/pgsql function?
Alex
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column&#
Greetings,
the following is an MySQL statement that I would like to
translate to PostgreSQL:
Could someone point me to a documentation of a coresponding
Systax for an "IF" clause in the a SELECT,
or is the some other way to do this
select
if(spektrum is null,' ','J'),
if(s19 is null,' ','
Are there performance advantages that can be achieved by wrapping a
complex SELECT into a stored procedure?
Alex
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
> > After digging through all the discussions of "INSERT waiting" problems I am
> > still not clear about the concensus about solving it.
> > ...
> > The only thing that I do not particulary like is that every INSERT
> > into this table has to adjust a counter column in a corresponding row of the
>
After digging through all the discussions of "INSERT waiting" problems I am
still not clear about the concensus about solving it.
I am running ration 6:1 SELECT:INSERT (insert fires up an UPDATE trigger
that hits a column in a table holding keys used by SELECT). I am looking at
doing about 2,00
the functions
execution, but currently only the whole function can be regarded as a
transaction, and nested transactions is not (yet) supported.
Some digging in the mailinglist archives pointed to isolation levels.
Apparently 'dirty reads' in theory:
[quote http://archives.postgresql.or
How can you use a distinct on () including the whole union.
eg
select distinct on (valutaid) valutaid, short from valuta UNION select
landid, land from land order by valutaid;
table: valuta
valutaidvaluta
1 USD
2 SEK
table: land
landid land
1
Thanks Cedar, Jan, and Andy.
Actually the setup is something like this...
There are two remote servers-remoteA and remoteB.
The table of remoteA needs to be sychronized with the
table of remoteB all the time (well, there could be an interval).
remoteB will *publish* every changes and remoteA is *
Hello Everyone,
Here's my simple question.
I just want to know/get the recent changes made to a table.
Deeper? I wanted the Postgresql server to *publish* every
changes made to a table (similar to replication, incremental transfer,
etc.).
What is the best way to go about it?
My idea is to creat
you can also do it by a shell script
grantall.sh:
## start of grantall.sh ###
#!/bin/ash
SQL="SELECT relname FROM pg_class WHERE (relkind = 'r' OR relkind = 'S')
AND relname !~ '^pg_' ORDER BY relname"
OBJ=`psql -t -c "${SQL}" $1`
# OBJ=`echo ${OBJ} | sed 's/EOF//g'`
OBJ=`echo ${OBJ} | se
you can also do it by a shell script
grantall.sh:
## start of grantall.sh ###
#!/bin/ash
SQL="SELECT relname FROM pg_class WHERE (relkind = 'r' OR relkind = 'S')
AND relname !~ '^pg_' ORDER BY relname"
OBJ=`psql -t -c "${SQL}" $1`
# OBJ=`echo ${OBJ} | sed 's/EOF//g'`
OBJ=`echo ${OBJ} | se
You can use 'DBI'
from test.pl of DBD::Pg
# create large object from binary file
my ($ascii, $pgin);
foreach $ascii (0..255) {
$pgin .= chr($ascii);
};
my $PGIN = '/tmp/pgin';
open(PGIN, ">$PGIN") or die "can not open $PGIN";
print PGIN $pgin;
close PGIN;
# begin transaction
$dbh->{AutoCo
Thanks to you Tom and Thomas.
Now I know.
- sherwin
[EMAIL PROTECTED] writes:
>Ah ha (or rather, ha ha ha)! I'd suggest using the RPMs posted on the
>postgresql.org ftp site, which include a sample .rpmrc file which fixes
>disasterous bugs in Mandrake's default compiler settings for building
>RP
[EMAIL PROTECTED] writes:
>"pgsql-sql" <[EMAIL PROTECTED]> writes:
>> ERROR: copy: line 3910, Bad timestamp external representation
>'2000-01-05
>> 00:00:60.00+08'
>
>> Weird because those timestamps were generated by default now().
>
[EMAIL PROTECTED] writes:
>"pgsql-sql" <[EMAIL PROTECTED]> writes:
>> migrate=# select userid from users where userid = '[EMAIL PROTECTED]';
>>userid
>> -
>> [EMAIL PROTECTED]
>> admin
>> (2 rows)
&g
SELECT is returning bogus data.
migrate=# select version();
version
---
PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.95.3
(1 row)
migrate=# select userid from users where userid = '[EMAIL PROTECT
Is it safe to rename 'postgres' to any__name?
like
update pg_shadow set usename = 'any__name' where usename = 'postgres';
Sherwin
xid loop detected, giving up
My observation: The tree (directories) were created but 1131 large objects
were not there.
The lo_dump.index file is not readable
(contains garbage).
peace,
sherwin
[EMAIL PROTECTED] writes:
>
>On Wed, 1 Nov 2000, pgsql-sql
You can try the script I made for exporting all my Pg database.
Ideas were borrowed from pg_dumplo-0.0.5.
Make it sure that you have "Perl5 extension for PostgreSQL - Pg.pm"
installed.
sherwin
#!/usr/bin/perl -w
#
# Export large objects of all Pg database
# - Sherwin T. Daganato ([EMAIL PROTEC
FROM test.pl of DBD-Pg-0.93.tar ...
# test large objects
# create large object from binary file
my ($ascii, $pgin);
foreach $ascii (0..255) {
$pgin .= chr($ascii);
};
my $PGIN = '/tmp/pgin';
open(PGIN, ">$PGIN") or die "can not open $PGIN";
print PGIN $pgin;
close
Hi All,
I'm planning to have data in a tree structure when fetched.
e.g.
NODE1
+ --- NODE1_1
+ --- NODE1_2
|+ --- NODE1_2_1
+ --- NODE1_3
Is this possible? How?
I would really appreciate any help.
Sherwin
Poul L. Christiansen writes:
> Isn't easier to reduce the table every day and make a daily vacuum which only
> lasts a few seconds?
I doubt that it would last just a few seconds. From my experience, VACUUM
on large tables can sap your I/O subsystem, slowing down overall
performance for everyone
[EMAIL PROTECTED] writes:
>How does the output of the above differ from:
>
>SELECT name FROM office, office_application
>WHERE code = office_code
>AND active != 't';
>
>Without knowing the table structures (which tables to active, code,
>and office_code belong to?) it's hard to suggest much els
HI!
The SQL below is too slow.
SELECT name FROM office, office_application
WHERE code = office_code
AND name NOT IN
(SELECT DISTINCT name FROM office, office_application
WHERE active = 't' AND code = office_code);
Can anyone tell me how to optimize it?
Thanks.
How do I use LIKE to search for strings with an underscore? The
documentation (well, Bruce's book) says to use 2 underscores (__) but it
doesn't work. For example:
create table liketest (
somestr varchar(50)
);
insert into liketest values ('foo_bar');
insert into liketest values ('foobar');
38 matches
Mail list logo
