[SQL] varchar::timezone conversion
Hi, I'm facing an issue, where I build time stamps from a set of strings. In this non working example, I'm looking for a way to cast my strings to the expected format 'timestamp' and timezone... CREATE OR REPLACE FUNCTION "public"."timestamp_to_utcms" (varchar,varchar) RETURNS int8 AS $body$ SELECT EXTRACT(EPOCH FROM $1 AT TIME ZONE $2)::int8*1000; $body$ LANGUAGE 'sql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER; Thanks for your help, Marc Mamin; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] exclusion query
Hi, I've got five related tables: - person_type: id_person_type integer type_fr text - person: id_person integer ... - person_to_event: id_person -> person id_person_type -> person_type (e.g: actor, director, producer, ...) id_event-> event - event: id_eventinteger id_event_type -> event_type ... - event_type: id_event_type integer type_fr text To select person_type's used in a certain event_type I have this query: select distinct pt.type from person_type pt natural join person_to_event join event e using (id_event) natural join event_type et where et.type_fr='théâtre'; Now, I'd like to select person_type's _not_ used in a certain particular event (say id_event=219). I can see how to build a quey to that effect, but is there a more obvious, clean, short solution? Something that looks like the above query maybe? Thanks, -- 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] exclusion query
On Mon, Sep 22, 2008 at 04:34:14PM +0200, Louis-David Mitterrand wrote: > Hi, > > I've got five related tables: > > - person_type: > id_person_type integer > type_fr text > > - person: > id_person integer > ... > > - person_to_event: > id_person -> person > id_person_type -> person_type (e.g: actor, director, producer, ...) > id_event-> event > > - event: > id_eventinteger > id_event_type -> event_type > ... > > - event_type: > id_event_type integer > type_fr text > > To select person_type's used in a certain event_type I have this query: > > select distinct pt.type > from person_type pt > natural join person_to_event > join event e using (id_event) > natural join event_type et > where et.type_fr='théâtre'; > > Now, I'd like to select person_type's _not_ used in a certain particular > event (say id_event=219). To be more precise: not used in a particular event _but_ used in other events of type 'theatre'. > I can see how to build a quey to that effect, but is there a more > obvious, clean, short solution? Something that looks like the above > query maybe? > > 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] exclusion query
On Mon, 2008-09-22 at 16:34 +0200, Louis-David Mitterrand wrote: > > > To select person_type's used in a certain event_type I have this > query: > > select distinct pt.type > from person_type pt > natural join person_to_event > join event e using (id_event) > natural join event_type et > where et.type_fr='théâtre'; > > Now, I'd like to select person_type's _not_ used in a certain > particular > event (say id_event=219). > > I can see how to build a quey to that effect, but is there a more > obvious, clean, short solution? Something that looks like the above > query maybe? Taking your second email into account, I came up with: select distinct pt.type_fr from person_to_event pte inner join person_type using (id_person_type) where id_person_type in ( select id_person_type from person_to_event pte inner join event using (id_event) inner join event_type using (id_event_type) where type_fr = 'theatre' ) and id_person_type not in ( select id_person_type from person_to_event where id_event = 219 ) I feel like there's a solution involving group by tugging at the back of my mind, but I can't quite put my finger on it. Sorry if this isn't quite what you're asking for. -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Multi-line text fields
Hi all, I have some records that have some multiple lines within a single text field. (On top of that, I think I have mixed DOS/UNIX line endings too). I'm looking for two functions which can operate on a single field: 1. number of lines 2. a way to select the first line or to trim to the first line only (the normal trim function doesn't appear to do this) Thanks in advance. -Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Special grouping on sorted data.
Hi, I was looking for a simple solution to this problem. I can't find a way to group on b and n by just collapsing sequential n's (identical n's right next to each other) the sorting condition is the timestamp. b| n | stamp A| 1 | 2008-09-20 06:07:47.981445 A| 1 | 2008-09-20 06:08:13.294306 A| 1 | 2008-09-20 06:12:02.046596 A| 2 | 2008-09-20 06:12:26.267786 A| 2 | 2008-09-20 06:12:47.750429 A| 1 | 2008-09-20 06:13:12.152512 A| 2 | 2008-09-20 06:13:39.052528 A| 2 | 2008-09-20 06:14:12.875389 B| 1 | 2008-09-20 06:14:29.963352 B| 1 | 2008-09-20 06:14:52.247307 B| 3 | 2008-09-20 06:15:13.358151 B| 3 | 2008-09-20 06:15:44.307792 B| 3 | 2008-09-20 06:16:17.32131 B| 2 | 2008-09-20 06:16:44.030435 B| 2 | 2008-09-20 06:17:00.140907 C| 1 | 2008-09-20 06:17:50.067258 C| 1 | 2008-09-20 06:18:22.280218 C| 1 | 2008-09-20 06:18:41.661213 C| 1 | 2008-09-20 06:19:07.920327 C| 3 | 2008-09-20 06:19:26.166675 C| 2 | 2008-09-20 06:19:46.459439 C| 2 | 2008-09-20 06:20:04.634328 Here I give an example of the output I'm looking for, And I can find a way to do that in crystal report, but I would like postgresql to send it that way. If the next n is different create a new row. b| n |min_stamp | max_stamp - A| 1 | 2008-09-20 06:07:47.981445 | 2008-09-20 06:12:02.046596 A| 2 | 2008-09-20 06:12:26.267786 | 2008-09-20 06:12:47.750429 A| 1 | 2008-09-20 06:13:12.152512 | 2008-09-20 06:13:12.152512 A| 2 | 2008-09-20 06:13:39.052528 | 2008-09-20 06:14:12.875389 B| 1 | 2008-09-20 06:14:29.963352 | 2008-09-20 06:14:52.247307 B| 3 | 2008-09-20 06:15:13.358151 | 2008-09-20 06:16:17.32131 B| 2 | 2008-09-20 06:16:44.030435 | 2008-09-20 06:17:00.140907 C| 1 | 2008-09-20 06:17:50.067258 | 2008-09-20 06:19:07.920327 C| 3 | 2008-09-20 06:19:26.166675 | 2008-09-20 06:19:26.166675 C| 2 | 2008-09-20 06:19:46.459439 | 2008-09-20 06:20:04.634328 If I write a GROUP BY b,n there is going to be only two rows for b = 'A', if I use distinct on i get the same thing is there an easy way to do that ? Nicolas -- 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] Special grouping on sorted data.
Nicolas Beuzeboc <[EMAIL PROTECTED]> writes: > I was looking for a simple solution to this problem. I can't find a way > to group on b and n by just collapsing sequential n's (identical n's > right next to each other) the sorting condition is the timestamp. I'm not totally certain that I understand what you need, but it sounds like the SELECT DISTINCT ON feature might solve it for you. Look into our SELECT reference page at the "weather reports" example. regards, tom lane -- 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] Multi-line text fields
Mike Toews wrote:
Hi all,
I have some records that have some multiple lines within a single text
field. (On top of that, I think I have mixed DOS/UNIX line endings too).
I'm looking for two functions which can operate on a single field:
1. number of lines
A few different ways, none of which are particularly pretty:
SELECT length(regexp_replace(inputstr, E'[^\\n]', '', 'g'));
SELECT count(1) from regexp_split_to_table(inputstr, E'\\n');
CREATE LANGUAGE plpythonu;
CREATE OR REPLACE FUNCTION nlines(s VARCHAR) RETURNS INTEGER AS $$
return len(s) - len(s.replace('\n',''))
$$ LANGUAGE 'plpythonu' IMMUTABLE;
SELECT nlines(inputstr);
Note that using \n should be OK so long as you don't have any files with
Mac line endings. It'll be fine for both UNIX and DOS line endings.
If you really need it to be fast, consider writing a small C function to
do the job. I'd consider an SQL-visible prototype like:
countchar(inputstr VARCHAR, findchar CHAR) RETURNS INTEGER
You might even want to send a patch in, just in case the dev team want
to include it as a utility function.
2. a way to select the first line or to trim to the first line only
(the normal trim function doesn't appear to do this)
One way, again probably not the fastest:
SELECT (regexp_split_to_array(inputstr, E'\\n'))[1]
Note the extra set of parentheses. You might also want to trim() off any
trailing \r in case of DOS line endings.
A little C function that copied the input only up to the first newline
would instead probably be the fastest. It'd also let you easily strip
the trailing \r if any was present.
--
Craig Ringer
--
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] Special grouping on sorted data.
Nicolas Beuzeboc wrote: Hi, I was looking for a simple solution to this problem. I can't find a way to group on b and n by just collapsing sequential n's (identical n's right next to each other) the sorting condition is the timestamp. b| n | stamp A| 1 | 2008-09-20 06:07:47.981445 [1] A| 1 | 2008-09-20 06:08:13.294306 [1] A| 1 | 2008-09-20 06:12:02.046596 [1] A| 2 | 2008-09-20 06:12:26.267786 [2] A| 2 | 2008-09-20 06:12:47.750429 [2] A| 1 | 2008-09-20 06:13:12.152512 [3] A| 2 | 2008-09-20 06:13:39.052528 [4] A| 2 | 2008-09-20 06:14:12.875389 [4] B| 1 | 2008-09-20 06:14:29.963352 [5] B| 1 | 2008-09-20 06:14:52.247307 [5] B| 3 | 2008-09-20 06:15:13.358151 [6] B| 3 | 2008-09-20 06:15:44.307792 [6] B| 3 | 2008-09-20 06:16:17.32131 [6] B| 2 | 2008-09-20 06:16:44.030435 [7] B| 2 | 2008-09-20 06:17:00.140907 [7] C| 1 | 2008-09-20 06:17:50.067258 [8] C| 1 | 2008-09-20 06:18:22.280218 [8] C| 1 | 2008-09-20 06:18:41.661213 [8] C| 1 | 2008-09-20 06:19:07.920327 [8] C| 3 | 2008-09-20 06:19:26.166675 [9] C| 2 | 2008-09-20 06:19:46.459439 [10] C| 2 | 2008-09-20 06:20:04.634328 [10] I'd be tempted to use a set-returning PL/PgSQL function to process an input set ordered by stamp and return a result whenever the (b,n) pair changed. I'm sure there's a cleverer set-oriented approach, but it's eluding me at present. You need a way to express the notion of "contiguous runs of (b,n)" which doesn't really exist in (set-oriented) SQL. Here I give an example of the output I'm looking for, And I can find a way to do that in crystal report, but I would like postgresql to send it that way. If the next n is different create a new row. I suspect that Crystal Reports may be pulling the whole data set from PostgreSQL then doing its processing client-side. Try turning on query logging in the server and running your report. See what SQL Crystal Reports actually executes. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
