[SQL] SETOF
Hi, I am using Postgresql version 7.2.2 I made a small function... CREATE FUNCTION ejem1(varchar) RETURNS SETOF to varchar as' SELECT names from mi_tabla WHERE city = $1; ' language ' SQL '; ejem1 Sergio Carlos Fernando When wanting to obtain several columns I do this... CREATE FUNCTION ejem2(varchar) RETURNS SETOF mi_tabla as' SELECT * from mi_tabla WHERE city = $1;' language ' SQL '; ejem2 --- 137956448 137956448 137956448 The number of registries that return is the correct, the question is, because it does not return the fields of the table, and that is what in its place this showing to me... Greetings and thank you very much! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Convert int to hex
I want to be able to change an int4 from a sequence and store it as varchar in the database as a hex number. Is this possible? Thank you, Fernando ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Convert int to hex
Thank you, I found out after I posted the message. I did read the docs but must have looked somewhere else and I search the lists for hex only and it did not bring the answer back. It wasn't until I search for int4 to hex that I was able to find the answer. Thank you again. Fernando On 6/1/05, Tony Wasson <[EMAIL PROTECTED]> wrote: > On 6/1/05, Fernando Grijalba <[EMAIL PROTECTED]> wrote: > > I want to be able to change an int4 from a sequence and store it as > > varchar in the database as a hex number. > > > > Is this possible? > > > > Thank you, > > > > Fernando > > Sure you can go from integer to hex and back... > http://www.varlena.com/varlena/GeneralBits/104.php > > Here's the example queries to get you started. > >=# select to_hex(11); > to_hex > > b >(1 row) > >=# select x'ab'::integer; > int4 >-- > 171 >(1 row) > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Problema con migracion de SQL a PostgreSQL
Subject: Migracion de SQL Server 2000 a PostgreSQL hola a todos, tengo un problema hace unos dias y necesito resolverlo cuanto antes. Necesito migrar la Base de Datos de un Portal Corporativo que desarrolle en SQL a Postgresql, pero no encuentro manera de hacerlo, trate de hacerlo por el export de SQL SErver 2000 pero no me exporta los SP, y para colmo tengo mas de 40 Procedimientos Almacenados (SP) que no quisiera reimplementar ademas que no tendria sentido. Yo se que Postgresql no tiene procedimientos almacenados de forma literal pero si tiene funciones o algo para encapsular consultas y devolver atributos. Necesito que si alguien ha resuelto esto me diga que hizo pues me urge saber. Gracias a todos. Me pueden responder por aqui [EMAIL PROTECTED]
[SQL] Como ejecutar una funcion insert en plpgsql....
Hola a todo... necesito ejecutar una consulta Insert que realice en pgsql para ver si inserta correctamente en la tabla asociada, pero con execute me da un erroralguien me puede decir como lo hago yo trabajo con el editor postgresql manager pro.. Gracias... Ahh otra cosa: en otra consulta que realice pero que es un select al principio me daba un error porque no encontraba la columna "ItemID" la cual porsupuesto existia, entonces a la columna le quite las mayusculas de su nombre quedando "itemid" y ya no me salio el error.en postgres los nombres de tablas y columnas deben ser siempre con minuscula
[SQL] OUT OF THIS LIST......
quiero darme de baja de la lista.mandenme el vinculo para hacerlo.
Re: [SQL] Substring
> > > > Given that tablename is "voipdb"; I wonder if OP really > wants to write > > a query that finds the row where argument to function > matches the most > > number of leading characters in "prefix". > > > > If voipdb table contains: ab, abc, def, defg; then calling > function > > with "abc" or "abcd" returns "abc" and calling function with "defh" > > returns "def". > > > > If this is the real problem to be solved; then brute force is one > > solution; but I'm left wondering if a single query might return > > desired result (a single row). > > Something like this may help in that case (note, we're > completely in the realm of creating imaginary problems and > solving them now :) > > select * from voipdb where prefix <= string order by prefix > desc limit 1; > > Regards, > > -- Raju Hum, I wonder if some kind of best-matching query is what you are looking for: SELECT * FROM voipdb WHERE prefix IN ( SELECT substr(string, 1, i) FROM generate_series(1, length(string)) i ); Cheers, Fernando. -- 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] Is there any function to test for numeric ips?
> -Mensaje original- > De: de Oliveiros C, > > Dear All, > > I have a table with host names and some happen to be numeric IPs. > > I would like to be able to filter out the later. > > Is there any function pre-defined in the system that can test > a particular text type value to see if it is a numeric ip? > > Something that returns true if applied to '192.168.1.1' but > false if applied to 'videos.sapo.pt' ? > > I considered NOT LIKE '%.%.%.%' but I'm affraid it will > filter out host names like 'www.google.com.br' > > I've realized that, for ex, inet 'x.x.x.x' will fail if the > input is not a numeric IP, is there any simple and direct way > to somewhat trap that error and convert it to a false value > that can be used in a WHERE clause? > You could filter IP out with a regular expression: select hostname as hosts_not_ip from table where hostname !~ '^[0-9]\.[0-9]\.[0-9]\.[0-9]$' Cheers, Fernando. -- 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] Is there any function to test for numeric ips?
>
> You could filter IP out with a regular expression:
>
> select hostname as hosts_not_ip
> from table
> where hostname !~ '^[0-9]\.[0-9]\.[0-9]\.[0-9]$'
>
Oops, i missed something. Its:
select hostname as hosts_not_ip
from table
where hostname !~ '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$'
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] check_constraint and Extract not working?
Just to be sure I am getting this right: I have a big table I want to partition: create table big_table ( row_date timestamp with time zone, row_data character varying(80) }; A nice solution would be to spread its rows in one of 12 child tables according to which month the date field belongs to. So my parent table is partitioned into 12 childs, one for each month: child_1, child_2, ..., child_12. My check constraints go like this: ALTER TABLE child_1 ADD CONSTRAINT chk_child1_month CHECK (EXTRACT(MONTH FROM row_date = 1::DOUBLE PRECISION); ALTER TABLE child_2 ADD CONSTRAINT chk_child2_month CHECK (EXTRACT(MONTH FROM row_date) = 2::DOUBLE PRECISION); ... Well, the check_constraint exclusion won't work with these. I assume the function Extract is the problem here and haven't been able to find a workaround. I have seen several examples where a table is partitioned by date but in those cases the year is also specified. ie: child_200612, child_200701, child_200702, etc. Though with this scenario I can avoid date functions in the check constraint, this would force me to keep creating new child tables from time to time. I would really like to avoid that kind of maintenance, and for my case 12 partitions are quite enough. Any suggestions how to achieve this otherwise? Regards, Fernando.
Re: [SQL] subtract a day from the NOW function
>> B) SELECT * FROM some_table WHERE to_char(some_timestamp, >> 'MMDD') > >> to_char((now() - interval '1 day'), 'MMDD'); > >I'd never use to_char to compare dates. The built-in comparison >operators work just fine. > Why not? I'm curious if has anything to do with performance or just style? Any difference between: ... WHERE to_char(my_date_col:date, '.MM.DD') < '2007.06.07' and ... WHERE my_date_col:date < '2007.06.07' Is there a 3rd better way to do this comparison? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Constraint exclusion
Index Cond: ((setuptime >= '2007-04-01 00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01 00:00:00-03'::timestamp with time zone)) -> Index Scan using idx_table_p06_setuptime on table_p06 table (cost=0.00..3.04 rows=1 width=273) Index Cond: ((setuptime >= '2007-04-01 00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01 00:00:00-03'::timestamp with time zone)) (28 rows) The plan shows that it scans the indexes for all partitions when it should only scan indexes for partitions 4 and 5. Is my assumption correct? If it is, could someone point me out what I am doing wrong? I can't figure out why it doesn't work. I think the caveats mentioned in the manual about constraint exclusion have been taken into account here but I might have missed something. Regards, Fernando. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Constraint exclusion
I see. Thanks for the tip. Regards, Fernando. -Mensaje original- De: Tom Lane [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 20 de Junio de 2007 19:37 Para: Fernando Hevia CC: 'PostgreSQL SQL List' Asunto: Re: [SQL] Constraint exclusion "Fernando Hevia" <[EMAIL PROTECTED]> writes: > -- Constraints: one partition per month > ALTER TABLE table_p01 ADD CONSTRAINT chk_table_p01_setuptime CHECK > (EXTRACT(MONTH FROM setuptime) = 1::DOUBLE PRECISION); The planner is not able to do anything with these constraints, other than if there is an exact match to them in the query WHERE, which there is not. Try simple range constraints on the column, instead. The system does know about inferences like "colx <= const1 must imply colx <= const2 if const1 <= const2". It does not know how to reason about extract(). regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] simple SQL question
> > I have a column with the following values (example below) > > > > 5673 > > 4731 > > 4462 > > 5422 > > 756 > > 3060 > > > > I want the column to display the numbers as follows: > > > > 56.73 > > 47.31 > > 44.62 > > 54.22 > > 7.56 > > 30.60 > > > > I have been playing around with string functions but cannot seem to > > figure out a quick solution. Does anyone have any suggestions? > > Don't use to_char unless you actually want character data though. > > Just try this... > > SELECT (column/100) FROM table; Cast the column in order to get the decimal part: SELECT (column::real/100) FROM table; ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] NO DATA FOUND Exception
Hi. Im taking my first steps with plpgsql. I want my function to react to the result of a query in the following way: begin select column into variable from table where condition; exception when <> then return variable; when <> then <> ; when <> then <> ; end ; Is something like this possible en plpgsql without recurring to a select count(*) to check how many results I will get? Actual code is: CREATE OR REPLACE FUNCTION test(p_line text) RETURNS text AS $body$ DECLARE v_len integer DEFAULT 8; v_search varchar; v_register num_geo%ROWTYPE; BEGIN -- Search loop WHILE v_len > 0 LOOP v_search := substring(p_line, 1, v_len); begin SELECT * INTO v_register WHERE prefix = v_search; exception when no_data then -- Getting error here continue; when others then return v_register.prefix; end; v_len := v_len - 1; END LOOP; raise 'Not found'; END; $body$ LANGUAGE 'plpgsql' VOLATILE ; ERROR: unrecognized exception condition "no_data" SQL state: 42704 Context: compile of PL/pgSQL function "test" near line 14 Thanks, Fernando. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] NO DATA FOUND Exception
>On Mon, Jun 25, 2007 at 04:20:37PM -0300, Fernando Hevia wrote: >> when <> then return variable; >> when <> then <> ; >> when <> then <> ; > >Check out the FOUND variable in the documentation for the first two, >and the "trapping errors" section for the latter. > >Andrew Sullivan | [EMAIL PROTECTED] Thanks for the tip. I was looking in the wrong place. The FOUND variable is explained in chapter "37.6.6. Obtaining the Result Status". Thanks again, Fernando. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] NO DATA FOUND Exception
On Jun 25, 2007, at 17:05, Michael Glaesemann wrote:
>[Please create a new message to post about a new topic, rather than
>replying to and changing the subject of a previous message. This will
>allow mail clients which understand the References: header to
>properly thread replies.]
Wasn't aware of this. Will do.
I should obtain a better mail client.
>However, it looks like you're trying to return a set of results
>(i.e., many rows), rather than just a single row. You'll want to look
>at set returning functions. One approach (probably not the best)
>would be to expand p_line into all of the possible v_search items and
>append that to your query, which would look something like:
Thank you for your help. All the advice was very useful and I have now a
working function.
I still have an issue left: I would like my function to return multiple
values (as in columns of a row).
Actually I found two possibilities: array and record. I ended up using
arrays since I couldn't figure out how to access the record data from
outside the function. Nevertheless I think a solution based on returning a
record type when you actually want to return the whole row would be more
elegant.
For example:
CREATE TABLE table1 (
field1 text,
field2 text,
field3 text
);
INSERT INTO table1 ('data1', 'data2', 'data3');
CREATE FUNCTION my_func() RETURNS record AS
$body$
DECLARE
v_row table1%ROWTYPE;
BEGIN
SELECT *
INTO v_row
FROM table1
WHERE ;
IF FOUND THEN
RETURN v_row;
END IF;
RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql';
SELECT my_func();
my_func
---
(data1, data2, data3)
How do I refer a specific field of the returned row from outside the
function? How should I write the query in order to show only fields 1 and 3,
for example?
It's sad to bother with this syntax questions, but I've had a hard time
finding code examples online.
Regards,
Fernando.
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] NO DATA FOUND Exception
>>"Fernando Hevia" <[EMAIL PROTECTED]> 2007-06-26 16:25 >>>
>>How do I refer a specific field of the returned row from outside the
>>function? How should I write the query in order to show only fields 1 and
3, for example?
>In case you would like to use set returning functions...
>
>if your function will return records with the same structure as an existing
>table
>CREATE FUNCTION my_func() RETURNS SETOF my_table AS ...
>
>if not you have to define the returning type
>CREATE TYPE func_row AS ("field1" varchar(10), "field2" integer, "field3"
>integer, ...)
>CREATE FUNCTION my_func() RETURNS SETOF func_row AS ...
>
>now you can use your function
>SELECT * FROM my_func();
>
>or
>
>SELECT A.field1, A.field2
>FROM my_func() A left join my_func() B on A.field2 = B.field3
>WHERE A.field1 like 'B%';
Exactly what I was looking for.
Thanks!!
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Inserting an IF statement in the middle of a SELECT in pl/pgSQL code
You should try 'Coalesce' function. By the way, your "incident_date-to-quarter" expression could be shortened: SELECT DISTINCT(pi.serial_number) AS "Incident ID", to_char(pi.incident_date,'Mon-dd-') AS "Incident date", to_char(pi.date_created,'Mon-dd-') AS "Report Date", (((EXTRACT (MONTH FROM pi.incident_date ))::integer - 1) / 3) + 1 AS Quarter ... Regards, Fernando. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] En nombre de Norm Garand Enviado el: Martes, 17 de Julio de 2007 23:43 Para: pgsql-sql@postgresql.org Asunto: [SQL] Inserting an IF statement in the middle of a SELECT in pl/pgSQL code Hi: I can't seem to resolve this issue. I have a fair sized SELECT statement that runs properly in a stored procedure using pl/pgSQL, but I have certain parts of the query that can filter by a NULL value, or a character string. What I can't seem to do is insert a conditional IF statement in my code. Here is how it starts: DECLARE r zrec_dd_holder%rowtype; BEGIN FOR r IN SELECT DISTINCT(pi.serial_number) AS "Incident ID", to_char(pi.incident_date,'Mon-dd-') AS "Incident date", to_char(pi.date_created,'Mon-dd-') AS "Report Date", CASE WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 1 THEN 1 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 2 THEN 1 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 3 THEN 1 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 4 THEN 2 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 5 THEN 2 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 6 THEN 2 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 7 THEN 3 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 8 THEN 3 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 9 THEN 3 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 10 THEN 4 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 11 THEN 4 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 12 THEN 4 END AS "Quarter", pf.name AS "Facility", pl.name AS "General Location", f.long_desc AS "Specific Location", .. The user can choose to allow the default (in this case NULL) or supply a predetermined value for "Specific Location". In the WHERE portion of the SELECT statment, I'd like to insert the IF statement shown below. I've tried single and double quotes and the pipeline for concatenation, but nothing seems to work. Any suggestions or resolutions would be greatly appreciated. Please feel free to contact me directly. AND pi.id = pid.id AND ( pid.incident_type_cid BETWEEN 117 AND 123 ) /--- IF $7 IS NOT NULL THEN AND f.id = pid.specific_location_cid AND f.long_desc = $7 END IF ---/ AND ( pi.location_id = pl.id ) AND pf.id = pl.facility_id AND pi.person_status_code_id = b.id regards, Norm ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Best Fit SQL query statement
Hi Depesz,
I was curious about your solution for Best Fit since I had mine working in a
function with a loop:
...
FOR v_len IN REVERSE v_max..v_min LOOP
v_prefix := substring(v_destino, 1, v_len);
SELECT * INTO v_result
FROM numeracion
WHERE prefijo = v_prefix;
IF FOUND THEN
RETURN :v_result;
END IF;
END LOOP;
...
Found your query is shorter and clearer, problem is I couldn't have it use
an index. Thought it was a locale issue but adding a 2nd index with
varchar_pattern_ops made no difference.
In result, it turned out to be too slow in comparison to the function. Am I
missing something?
--- DDL ---
rd=# show lc_collate;
lc_collate
-
en_US.UTF-8
(1 row)
rd=# show client_encoding;
client_encoding
-
SQL_ASCII
(1 row)
rd=# show server_encoding;
server_encoding
-
SQL_ASCII
(1 row)
rd=# \d numeracion
Table "public.numeracion"
Column|Type | Modifiers
-+-+---
cod_oper| integer |
servicio| text| not null
modalidad | text| not null
localidad | text| not null
indicativo | text| not null
bloque | text| not null
resolucion | text|
fecha | date| not null
prefijo | text| not null
largo | integer |
fecha_carga | timestamp without time zone | default now()
Indexes:
"pk_numeracion" PRIMARY KEY, btree (prefijo)
"idx_numeracion_prefijo" btree (prefijo varchar_pattern_ops)
Foreign-key constraints:
"fk_numeracion_operadores_cod_oper" FOREIGN KEY (cod_oper) REFERENCES
operadores(cod_oper)
rd=# set enable_seqscan = off;
SET
rd=# explain select prefijo
rd-# FROM numeracion
rd-# WHERE '3514269565' LIKE prefijo || '%'
rd-# ORDER BY LENGTH(prefijo) DESC
rd-# LIMIT 1;
QUERY PLAN
Limit (cost=11077.54..11077.54 rows=1 width=89)
-> Sort (cost=11077.54..11077.91 rows=151 width=89)
Sort Key: length(prefijo)
-> Seq Scan on numeracion (cost=1.00..11072.07
rows=151 width=89)
Filter: ('3514269565'::text ~~ (prefijo || '%'::text))
Why I am getting these monstrous costs? Table had been vacuumed full just
before running the explain plan. It has ~31k rows.
Any hindsight will be greatly appreciated.
Regards,
Fernando.
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
En nombre de hubert depesz lubaczewski
Enviado el: Viernes, 10 de Agosto de 2007 05:00
Para: Kiran
CC: pgsql-sql@postgresql.org
Asunto: Re: [SQL] Best Fit SQL query statement
On Mon, Aug 06, 2007 at 01:57:07AM -0700, Kiran wrote:
> Could anyone help me in writing Best Fit SQL statement.
> Suppose we have table t1 with coloumn t1 (text) with following rows.
> 98456
> 98457
> 9845
> 9846
> 984
> 985
> 98
> 99
> and if I query on 98456 the result must be 98456,
> However if I query on 98455 the result must be 9845
> and If I query 9849 the result must be 984
select t1.t1 from t1 where '98456' like t1.t1||'%' order by length(t1.t1)
desc limit 1;
should be ok.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Best Fit SQL query statement
De: hubert depesz lubaczewski [mailto:[EMAIL PROTECTED]
>>On Fri, Aug 10, 2007 at 04:40:34PM -0300, Fernando Hevia wrote:
>> Found your query is shorter and clearer, problem is I couldn't have it
use
>> an index. Thought it was a locale issue but adding a 2nd index with
>> varchar_pattern_ops made no difference.
>> In result, it turned out to be too slow in comparison to the function. Am
I
>> missing something?
>> rd=# explain select prefijo
>> rd-# FROM numeracion
>> rd-# WHERE '3514269565' LIKE prefijo || '%'
>> rd-# ORDER BY LENGTH(prefijo) DESC
>> rd-# LIMIT 1;
> unfortunatelly this query will be hard to optimize.
> i guess that functional approach will be the fastest, but you can try
> with something like this:
>
> select prefijo
> from numeracion
> where prefijo in (
> select substr('3514269565',1,i)
> from generate_series(1, length('3514269565')) i
> )
> order by length(prefijo) desc LIMIT 1;
>
>it should be faster then the previous approach, but it will most
>probably not be as fast as function.
Actually, I find this variant nearly as fast as the function. The
generate_series can be limited to known minimum and maximum prefix lengths
in order to speed up the query a bit more.
Works quite well.
Cheers,
Fernando.
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] Problem with phone list.
Try this: Select * from view v1 where duration = (select max(duration) from view v2 where v2.phone_number = v1.phone_number) You could get more than one call listed for the same number if many calls match max(duration) for that number. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] En nombre de Mike Diehl Enviado el: Miércoles, 15 de Agosto de 2007 17:28 Para: SQL Postgresql List Asunto: [SQL] Problem with phone list. Hi all. I've qot a problem I need to solve. I'm sure it's pretty simple; I just can't seem to get it, so here goes... I've got a table, actually a view that joins 3 tables, that contains a phone number, a unique id, and a call duration. The phone number has duplicates in it but the unique id is unique. I need to get a list of distinct phone numbers and the coorisponding largest call duration. I've got the idea that this should be a self-join on phone number where a.id<>b.id, but I just can't seem to get the max duration. Any hints would be much appreciated. Mike. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Problem with phone list.
--- Michael Glaesemann wrote: > SELECT DISTINCT ON (phone_number) > phone_number, call_duration, id > FROM calls > ORDER BY phone_number > , call_duration DESC; Wasn't acquainted with "DISTINCT ON (column)". I found it to be many times faster than other suggestions using JOIN. Cheers, Fernando. ---(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's datatypes do not match
[SQL] Function Volatility
Hi guys, I am not sure if I am understanding volatility. My issue is better explained with a quick example. The function below expresses call durations in minutes and it is immutable. CREATE OR REPLACE FUNCTION dur2min(secs INTEGER) RETURNS INTEGER AS $$ BEGIN RAISE NOTICE 'BEEN HERE!'; RETURN CEIL(secs/60.0); END; $$ LANGUAGE 'plpgsql' IMMUTABLE; # SELECT dur2min(30) as c1, dur2min(30) as c2, dur2min(30) as c3; NOTICE: BEEN HERE! NOTICE: BEEN HERE! NOTICE: BEEN HERE! c1 | c2 | c3 ++ 1 | 1 | 1 (1 row) What bother me are the 3 "been here" messages. As the function is immutable and the parameter remains unchanged needs the planner actually execute the function 3 times? I was under the impression that under these conditions it could *reuse* the result of the first call. The manual states the planner should avoid reevaluate the function but I'm not sure what that means as it *is* executing it every time. My goal of course is that the function gets executed only once per row. I'm using 8.2.4 Thanks for your hindsight. Regards, Fernando.
Re: [SQL] Function Volatility
Tom Lane writes: > The IMMUTABLE marker is a promise from you to the system that it is safe > to optimize away multiple calls to the function. It is not a promise > from the system to you that the system will expend unlimited amounts of > energy to detect duplicate calls. Nicely put. Thanks! BTW, this explanation should go into the manual. Regards, Fernando. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] request for help with COPY syntax
> -Mensaje original- > De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > En nombre de Chuck D. > > Anyone known how I can rewrite the COPY command to allow those " or ' > within > the data? After a couple days I wasn't able to find any examples to help. > Hi Chuck, Do you need those characters in your table? If not I think you will be better off preprocessing the data before running copy. Replacing those " for ' or directly removing them is quite simple if you are working in Unix, actually it should be quite simple in any operating system. Regards, Fernando ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] request for help with COPY syntax
> De: Chuck D. > > I'm not sure if they are needed because I've never seen a double quote in > a > place name before. I don't believe they are errors though because there > are > more records that contain them. As well, some records have single and > double > quotes allowed within a record and this really messes things up. > > Any ideas? Should I consider removing them in favor of a single quote? > If > so, do you know how to do this with sed or similar? > Well, hard to say what to do with those quotes without knowing if your query conditions for places will include them. I probably would replace them for an underscore or any other uniquely identifiable character in order to succeed with the copy, and keep the possibility to later decide if that underscore becomes again a quote or gets removed all together. If you would like to just remove single or double quotes you should do: sed "s/[\'\"]//g" file_with_quotes.txt > file_without_quotes.txt Say you want to replace quotes with a space, then: sed "s/[\'\"]/ /g" file_with_quotes.txt > file_without_quotes.txt Insert whatever you want to replace the quotes between the 2nd and 3rd bar (/). Regards, Fernando. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] request for help with COPY syntax
> On October 25, 2007 10:57:49 am you wrote:
> >
> > If all you just want to do is strip out the ^M, you can run dos2unix on
> > it, assuming that you are running a *nix distro.
>
> Well, I guess I could strip the ^M but I'm still left with a $ in the
> middle
> of a field which in the same as the line terminator, so COPY thinks it is
> at
> the end of a line when it is really in the middle of the field. I really
> wish they would have quoted these fields, but I'm at a loss how to import
> these.
>
As I understand it when a line starts with $ you would like to merge it with
the previous line.
I suppose you have a file like this:
--- test.txt ---
this is
$field1, and this is
$field2
I'll create the test file:
$ printf "this is \n\$field1, and this is \n\$field2\n" > test.txt
(I assume ^M have already been replaced so \n are used instead)
A short C program should do it:
/*-- code listing -*/
#include
#include
#define NL '\n'
#define FILTER '$'
int main(int argc, char *argv[]) {
FILE *fp;
char c;
if (argc < 2) fp=stdin;
else {
fp=fopen(argv[1], "r");
if (!fp) {
perror(argv[1]);
exit(1);
}
}
c=fgetc(fp);
while(!feof(fp)) {
if(c==NL) {
c=fgetc(fp);
if(feof(fp)) {
putchar(NL);
break;
}
}
if(c!=FILTER) putchar(c);
c=fgetc(fp);
}
exit (0);
}
/*--*/
compile as:
$ gcc -o test test.c
Execute as:
$ test test.txt
this is field1, and this is field2
Could this be of help?
Regards,
Fernando.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] Perfomance benefit using Min() against order by & limit 1?
Hi guys. Is there any difference between these two queries regarding performance? Table stopvoip has several million records. I suspect using the aggregate function would be best, but benchmarking doesn't seem to confirm it. Both queries take around 150 - 175 ms once data has been cached. Any hindsights? SELECT min(h323setuptime::date) FROM stopvoip WHERE callingstationid = '2941605118' AND h323setuptime >= '2007.07.01' AND h323disconnectcause = '10' AND acctsessiontime > 0 AND NOT calledstationid ~ '^99[89]#' "Aggregate (cost=11151.25..11151.27 rows=1 width=8)" " -> Bitmap Heap Scan on stopvoip (cost=29.29..11149.98 rows=507 width=8)" "Recheck Cond: ((callingstationid)::text = '2941605118'::text)" "Filter: ((h323setuptime >= '2007-07-01 00:00:00-03'::timestamp with time zone) AND ((h323disconnectcause)::text = '10'::text) AND (acctsessiontime > 0) AND ((calledstationid)::text !~ '^99[89]#'::text))" "-> Bitmap Index Scan on idx_stopvoip_callingid2 (cost=0.00..29.29 rows=2939 width=0)" " Index Cond: ((callingstationid)::text = '2941605118'::text)" SELECT h323setuptime::date FROM stopvoip WHERE callingstationid = '2941605118' AND h323setuptime >= '2007.07.01' AND h323disconnectcause = '10' AND acctsessiontime > 0 AND NOT calledstationid ~ '^99[89]#' ORDER BY 1 LIMIT 1 "Limit (cost=11174.03..11174.03 rows=1 width=8)" " -> Sort (cost=11174.03..11175.30 rows=507 width=8)" "Sort Key: (h323setuptime)::date" "-> Bitmap Heap Scan on stopvoip (cost=29.29..11151.25 rows=507 width=8)" " Recheck Cond: ((callingstationid)::text = '2941605118'::text)" " Filter: ((h323setuptime >= '2007-07-01 00:00:00-03'::timestamp with time zone) AND ((h323disconnectcause)::text = '10'::text) AND (acctsessiontime > 0) AND ((calledstationid)::text !~ '^99[89]#'::text))" " -> Bitmap Index Scan on idx_stopvoip_callingid2 (cost=0.00..29.29 rows=2939 width=0)" "Index Cond: ((callingstationid)::text = '2941605118'::text)" Thanks, Fernando.
Re: [SQL] Perfomance benefit using Min() against order by & limit 1?
Oops. Previous message went in HMTL. Sorry for that. Text-only version follows. --- Hi guys. Is there any difference between these two queries regarding performance? Table stopvoip has several million records. I suspect using the aggregate function would be best, but benchmarking doesnt seem to confirm it. Both queries take around 150 - 175 ms once data has been cached. Any hindsights? SELECT min(h323setuptime::date) FROM stopvoip WHERE callingstationid = '2941605118' AND h323setuptime >= '2007.07.01' AND h323disconnectcause = '10' AND acctsessiontime > 0 AND NOT calledstationid ~ '^99[89]#' Aggregate (cost=11151.25..11151.27 rows=1 width=8)" -> Bitmap Heap Scan on stopvoip (cost=29.29..11149.98 rows=507 width=8) Recheck Cond: ((callingstationid)::text = '2941605118'::text) Filter: ((h323setuptime >= '2007-07-01 00:00:00-03'::timestamp with time zone) AND ((h323disconnectcause)::text = '10'::text) AND (acctsessiontime > 0) AND ((calledstationid)::text !~ '^99[89]#'::text)) -> Bitmap Index Scan on idx_stopvoip_callingid2 (cost=0.00..29.29 rows=2939 width=0) Index Cond: ((callingstationid)::text = '2941605118'::text) SELECT h323setuptime::date FROM stopvoip WHERE callingstationid = '2941605118' AND h323setuptime >= '2007.07.01' AND h323disconnectcause = '10' AND acctsessiontime > 0 AND NOT calledstationid ~ '^99[89]#' ORDER BY 1 LIMIT 1 Limit (cost=11174.03..11174.03 rows=1 width=8) -> Sort (cost=11174.03..11175.30 rows=507 width=8) Sort Key: (h323setuptime)::date -> Bitmap Heap Scan on stopvoip (cost=29.29..11151.25 rows=507 width=8) Recheck Cond: ((callingstationid)::text = '2941605118'::text) Filter: ((h323setuptime >= '2007-07-01 00:00:00-03'::timestamp with time zone) AND ((h323disconnectcause)::text = '10'::text) AND (acctsessiontime > 0) AND ((calledstationid)::text !~ '^99[89]#'::text)) -> Bitmap Index Scan on idx_stopvoip_callingid2 (cost=0.00..29.29 rows=2939 width=0) Index Cond: ((callingstationid)::text = '2941605118'::text) Thanks, Fernando. ---(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's datatypes do not match
Re: [SQL] design of tables for sparse data
> --- Andreas Wrote: --- > ... > > MY QUESTIONS: Your questions have a strong "home-work" look. > > 1) How would I SELECT a report that looks like the first version of > the pupil table out of the 3 table design? > There must be a nontrivial SELECT statement that combines all 3 tables. > E.g. I want the result: > pupil_id, pupil_name, attends_to_english, ., attends_to_football, > attends_to_swimming, attends_to_knitting > (42, Frank Miller, yes, , no, yes, yes) > (43, Suzy Smith, yes, ..., yes, yes, no) > ... You should check out the JOIN clause in select statements. Simple example: Select t1.col1, t2.col1, t2.col2 from t1 inner join t2 b on (t1.col1 = t2.col1) > > 2) Could I control the order in which those attends_to-columns appear > by a numerical field output_order? > You specify the order of output columns in the select statement. If you want to do this dynamically (say each user wants to configure its own order) you are really better of programming in your front-end application. No trivial solution in a pure SQL solution. It would probably require some dynamic sql and another table which holds de column printout order. > 3) Could I restrict the classes list so that only those appear when > there are pupils actually attending them in a given time frame? > 3) a) Like "competitve knitting" was only available from 2000-2005. > Now I'd produce a list of 2007 so there shouldn't appear an empty > knitting-column. --> classes.is_availlable > 3) b) Or it is availlable but no one has chosen it in 2007. --> > attends_to.in_year > Yes, you could. Read about different JOINS and WHERE clauses. ;) Regards, Fernando. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] PG is in different timezone than the OS
Hi all, I am not sure if this is the correct list to post this issue. Please let me know if there is a more suitable one. Argentina's government has recently decreted a timezone change for the summer (daylight's savings) where local time zone changes from GMT-3 to GMT-2. The Argentinean Summer Timezone is named "ARST". My first problem is that Postgres still hangs with GMT-3 while OS is at GMT-2 *OS date* # date -R ; date Wed, 02 Jan 2008 16:07:36 -0200 Wed Jan 2 16:07:36 ARST 2008 *Postgres* radius=# select now()::timestamp with time zone; now --- 2008-01-02 15:07:59.435233-03 (1 row) As you can see PG is at GMT-03. Restart has been done to no effect. Postgres.conf settings are: # - Locale and Formatting - #datestyle = 'iso, mdy' #timezone = unknown # actually, defaults to TZ # environment setting #australian_timezones = off #extra_float_digits = 0 # min -15, max 2 #client_encoding = sql_ascii# actually, defaults to database # encoding I have also tried with: timezone='America/Argentina/Cordoba' How do I tell postgres that it is located in Argentina/Cordoba or GMT-02? Is there a way to have it relay to the OS? My second problem is that Postgres doesn't recognize the timezone ARST. pg=# select '01:13:16.426 ARST Wed Jan 2 2008'::timestamp with time zone; ERROR: invalid input syntax for type timestamp with time zone: "01:13:16.426 ARST Wed Jan 2 2008" Whereas with the previous ART timezone it did well: pg=# select '01:13:16.426 ART Wed Jan 2 2008'::timestamp with time zone; timestamptz 2008-01-02 01:13:16.426-03 (1 row) I'm lost here. ARST isn't new. It has been used in former years. Any help would be greatly appreciated. Regards, Fernando ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Argentinian timezone change at the last moment. How to change pgsql tz db?
> Tom Lane wrote: > > "Scott Marlowe" <[EMAIL PROTECTED]> writes: > > That doesn't get me what I need. It lets me change the alias of > > timezones, but not the start and stop of daylight savings time. I > > think for that I'd have to edit / replace the files in > > postgresql-8.2.x/src/timezone/data/ and recompile to fix this. > > Since the OP has apparently already managed to get updated tzdata files > installed on his system, he could just copy them into > /usr/share/postgresql/timezone --- anything using zic should be a > compatible file format. > > The lack-of-ARST-on-input problem can be addressed by mucking with > /usr/share/postgresql/timezonesets/Default, if you're using 8.2. > In earlier versions the table is hardwired into datetime.c :-( > > regards, tom lane Thanks Scott and Tom for your help on this. After copying the updated tz file to /usr/share/postgresql/timezone Postgres got aware of the time change. Regarding the ARST recognition, I'm still on 8.1.9. :( An upgrade seems urgent now. Regards, Fernando. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Argentinian timezone change at the last moment. How to change pgsql tz db?
> Tom Lane [mailto:[EMAIL PROTECTED] wrote: > > Since the OP has apparently already managed to get updated tzdata files > installed on his system, he could just copy them into > /usr/share/postgresql/timezone --- anything using zic should be a > compatible file format. > > The lack-of-ARST-on-input problem can be addressed by mucking with > /usr/share/postgresql/timezonesets/Default, if you're using 8.2. > In earlier versions the table is hardwired into datetime.c :-( > Summing up: After installing the updated tzdata files in the server I had to copy the America/Argentina/* files to /usr/share/postgresql/timezone in order to get postgres determine the correct local time. With 8.2.x the ARST abbreviation was recognized after including the following line in /usr/share/postgresql/8.2/timezonesets/Default ARST -14400 D # Argentina Summer Time postgres=# select '01:13:16.426 ARST Wed Jan 2 2008'::timestamp with time zone; timestamptz 2008-01-02 01:13:16.426-02 (1 row) I wonder if pg_timezone_names plays any role in the ARST issue. It does contain the right data (appeared after copying tzdata into /usr/share/postgresql/timezone and restarting server) but ARST wasn't accepted till previous step was done. postgres=# select * from pg_timezone_names where abbrev = 'ARST'; name | abbrev | utc_offset | is_dst +++ localtime | ARST | -02:00:00 | t America/Argentina/Rio_Gallegos | ARST | -02:00:00 | t America/Argentina/Mendoza | ARST | -02:00:00 | t America/Argentina/La_Rioja | ARST | -02:00:00 | t America/Argentina/Buenos_Aires | ARST | -02:00:00 | t America/Argentina/Cordoba | ARST | -02:00:00 | t America/Argentina/Catamarca| ARST | -02:00:00 | t America/Argentina/Ushuaia | ARST | -02:00:00 | t America/Argentina/Tucuman | ARST | -02:00:00 | t America/Argentina/Jujuy| ARST | -02:00:00 | t America/Argentina/San_Juan | ARST | -02:00:00 | t (11 rows) Thanks for all contributions. Regards, Fernando. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Argentinian timezone change at the last moment. How to change pgsql tz db?
> Tom Lane [mailto:[EMAIL PROTECTED] wrote: > > "Fernando Hevia" <[EMAIL PROTECTED]> writes: > > With 8.2.x the ARST abbreviation was recognized after including the > > following line in /usr/share/postgresql/8.2/timezonesets/Default > > > ARST -14400 D # Argentina Summer Time > > Um ... is that really offsetting in the correct direction? What I put > into CVS was > > ARST-7200 D # Argentina Summer Time > > If that's wrong I need to know ... > No, you are right: -7200 is the correct offset. Regards, Fernando. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Update PK Violation
> Franklin Haut wrote: > > Hi all, > > i have a problem with one update sentence sql. > > example to produce: > > create table temp (num integer primary key, name varchar(20)); > > insert into temp values (1, 'THE'); > insert into temp values (2, 'BOOK'); > insert into temp values (3, 'IS'); > insert into temp values (4, 'ON'); > insert into temp values (5, 'THE'); > insert into temp values (6, 'RED'); > insert into temp values (7, 'TABLE'); > Couldn't figure out how to do it in one sentence, still it can be done with a function: CREATE OR REPLACE FUNCTION insert_value(p_num integer, p_name varchar(20)) RETURNS VOID AS $$ declare v_num integer; BEGIN FOR v_num in SELECT num FROM temp WHERE num >= p_num ORDER BY num DESC LOOP UPDATE temp SET num = num + 1 WHERE num = v_num; END LOOP; INSERT INTO temp VALUES (p_num, p_name); END; $$ LANGUAGE 'plpgsql' VOLATILE; To run it: sistema=# select insert_value(4, 'NOT'); insert_value -- (1 row) sistema=# select * from temp order by num; num | name -+--- 1 | THE 2 | BOOK 3 | IS 4 | NOT 5 | ON 6 | THE 7 | RED 8 | TABLE (8 rows) Regards, Fernando. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] First day of month, last day of month
> -Mensaje original- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] En nombre de Scott Marlowe > > Then you can just use date_trunc on the values in the > database. Plus if you're using timestamp WITHOUT timezone, > you can index on it. > Did not understand this. Are you saying timestamps WITH timezone are NOT indexable or you mean that you cant build a partial index on a timestamp-with-time-zone returning function? Regards, Fernando. -- 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] First day of month, last day of month
> -Mensaje original-
> De: Scott Marlowe [mailto:[EMAIL PROTECTED]
>
>
> Note that if you are storing your time stamp as timestamptz,
> you can use the "at time zone 'xyz'" construct to create an
> index, and as long as you retrieve them with the same
> construct you'll get to use the index.
>
> create index test_ts_month_trunc on testtable
> (date_trunc('month',(ts at time zone 'MST')));
> select * from testtable
> where date_trunc('month',(ts at time zone 'MST'))='2007-10-01
00:00:00'::timestamp;
>
I see the point. Thanks for the elaboration.
--
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] trim(both) problem?
> -Mensaje original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] En nombre de Stephan Szabo
> Enviado el: Viernes, 25 de Abril de 2008 17:46
> Para: Emi Lu
> CC: pgsql-sql@postgresql.org
> Asunto: Re: [SQL] trim(both) problem?
>
> On Fri, 25 Apr 2008, Emi Lu wrote:
>
> > Hi,
> >
> > Isn't this a bug about trim both.
> >
> > select trim(both '' from 'ROI Engineering Inc.');
> > btrim
> > -
> > OI Engineering Inc.
> > (1 row)
> >
> >
> > "R" is missing? How?
>
> Trim doesn't do what you think it does. The '' in the
> above is not a string to remove it is a list of characters to
> remove. Thus, the R is removed as it matches a character given.
>
You could probably use instead:
select replace('ROI Engineering Inc.', '', '')
--
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] Weeks elapsed to Months elapsed conversion
> > Hi all, > I have a simple question (tried googling but found no > answers). How do I convert weeks elapsed into months elapsed? > I have data that contains duration in weeks (without any > other date values such as year and so on) for example a week > value of 14 and I would like to convert the 14 weeks to 3 > months (some lose of accuracy expected). > Expected tests may be: > 14 weeks yields 3 months. > 1 weeks yields 0 months. > If accuracy isnt a issue probably floor() could suite you: months=select floor(weeks/4); Regards, Fernando. -- 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] using calculated column in where-clause
> -Mensaje original- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] En nombre de Patrick > Scharrenberg > Enviado el: Martes, 17 de Junio de 2008 17:46 > Para: pgsql-sql@postgresql.org > Asunto: [SQL] using calculated column in where-clause > > Hi! > > I'd like to do some calculation with values from the table, > show them a new column and use the values in a where-clause. > > Something like this > select a, b , a*b as c from ta where c=2; > > But postgresql complains, that column "c" does not exist. > > Do I have to repeat the calculation (which might be even more complex > :-) ) in the "where"-clause, or is there a better way? > For complex calculations I have obtained better performance using nested queries. For example: select a, b, c select ( select a, b, a*b as c from ta) subquery1 where c = 2; This nesting is probably overhead in such a simple case as this, but in more complex ones and specially with volatile functions it will provide an improvement. Regards, Fernando. -- 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] using calculated column in where-clause
> -Mensaje original- > De: Scott Marlowe [mailto:[EMAIL PROTECTED] > Enviado el: Miércoles, 18 de Junio de 2008 17:47 > Para: Fernando Hevia > > > > For complex calculations I have obtained better performance using > > nested queries. For example: > > > > select a, b, c select > > ( select a, b, a*b as c from ta) subquery1 where c = 2; > > > > This nesting is probably overhead in such a simple case as > this, but > > in more complex ones and specially with volatile functions it will > > provide an improvement. > > I was under the impresion from previous discussions that the > query planner flattened these out to be the same query. Do > you get different query plans when you re-arrange this way? > Take a look at this example (tried on 8.2.7 & 8.1.11): create or replace function test(p1 integer, p2 integer) returns integer[] as $BODY$ declare retval integer[]; begin raise info 'called test(%, %)', p1, p2; retval[0] = p1 + p2; retval[1] = p1 * p2; retval[2] = p1 - p2; return retval; end; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE; -- In this case function test is called three times: pg=# select (test(1, 2))[0] as sum, (test(1, 2))[1] as prod, (test(1, 2))[2] as dif; INFO: called test(1, 2) INFO: called test(1, 2) INFO: called test(1, 2) sum | prod | dif -+--+- 3 |2 | -1 (1 row) -- In this case function test is called only once: pg=# select res[0] as sum, res[1] as prod, res[2] as dif from pg-# (select (test(1, 2))::integer[] as res) t ; INFO: called test(1, 2) sum | prod | dif -+--+- 3 |2 | -1 (1 row) I assume the second form will perform better since test is being called only once. I might be missing something in this assumption but at first glance it seems pretty straightforward. Regards, Fernando. -- 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] using calculated column in where-clause
> > -Mensaje original- > > De: Scott Marlowe [mailto:[EMAIL PROTECTED] Enviado el: > > Miércoles, 18 de Junio de 2008 17:47 > > Para: Fernando Hevia > > > > > > > For complex calculations I have obtained better performance using > > > nested queries. For example: > > > > > > select a, b, c select > > > ( select a, b, a*b as c from ta) subquery1 where c = 2; > > > > > > This nesting is probably overhead in such a simple case as > > this, but > > > in more complex ones and specially with volatile > functions it will > > > provide an improvement. > > > > I was under the impresion from previous discussions that the query > > planner flattened these out to be the same query. Do you get > > different query plans when you re-arrange this way? > > > > Take a look at this example (tried on 8.2.7 & 8.1.11): > > create or replace function test(p1 integer, p2 integer) > returns integer[] as $BODY$ declare >retval integer[]; > begin >raise info 'called test(%, %)', p1, p2; >retval[0] = p1 + p2; >retval[1] = p1 * p2; >retval[2] = p1 - p2; >return retval; > end; > $BODY$ > LANGUAGE 'plpgsql' IMMUTABLE; > > -- In this case function test is called three times: > pg=# select (test(1, 2))[0] as sum, (test(1, 2))[1] as prod, > (test(1, 2))[2] as dif; > INFO: called test(1, 2) > INFO: called test(1, 2) > INFO: called test(1, 2) > sum | prod | dif > -+--+- >3 |2 | -1 > (1 row) > > > -- In this case function test is called only once: > pg=# select res[0] as sum, res[1] as prod, res[2] as dif from > pg-# (select (test(1, 2))::integer[] as res) t ; > INFO: called test(1, 2) > sum | prod | dif > -+--+- >3 |2 | -1 > (1 row) > > I assume the second form will perform better since test is > being called only once. > I might be missing something in this assumption but at first > glance it seems pretty straightforward. > > Regards, > Fernando. > --Follow up-- When I use one of the inner-query columns as a condition for the outer-query the function is being called again: pg=# select res[0] as sum, res[1] as prod, res[2] as dif from pg-# (select (test(1, 2))::integer[] as res) t pg-# where res[0] = 3; INFO: called test(1, 2) INFO: called test(1, 2) sum | prod | dif -+--+- 3 |2 | -1 (1 row) Seems this blows away my theory, at least part of it. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] column default dependant on another columns value
Hi list, Given a table with columns seconds and minutes, how can I have minutes be computed automatically at the insert statement? I tried: ALTER TABLE table1 ALTER COLUMN minutes SET default (seconds/60); Postgres' answer was: ERROR: cannot use column references in default expression So I gave rules a look but it seems rules apply to the entire row. CREATE RULE "my_rule" AS ON INSERT TO table1 WHERE minutes is null DO INSTEAD INSERT INTO table1 (column1, column2, seconds, minutes) VALUES(new.column1, new.column2, new.seconds, new.seconds/60); Is this correct? Is there another (better/simpler) way to achieve this? Regards, Fernando -- 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] column default dependant on another columns value
> -Mensaje original- > De: Richard Broersma [mailto:[EMAIL PROTECTED] > > It is possible to do this with a trigger or a rule. A > trigger would be more robust. > > > Is this correct? Is there another (better/simpler) way to > achieve this? > > Well I might work, but it is a bad practice to get into since > what you are trying to do violates the rules of database > normalization. > > Wouldn't it be better to calculate the minutes with you query > your table? > > SELECT *, seconds / 60 AS minutes > FROM yourtable; Actually I only used this as an example. The real table is queried lots of times for millions of rows and the server is showing some high-level user cpu consumption. There are a couple calculated columns on the table so I am trying to reduce cpu usage by pre-calculating the more cpu intensive data once on insert. Enhancing the application is currently not possible. Anyway, the rule didn't work. Got "an infinite recursion error" when inserting on the table. Can't figure out where the recursion is as supposedly the rule kicks in when the "where minutes is null" condition is satisfied. The DO INSTEAD part runs an insert were minutes is NOT null so the rule should be ignored. Where is the recursion then? I am on postgres 8.2.9. Thanks for your hindsight Richard. I Will look into the trigger solution. Still, I'd like to understand this recursion error. Regards, Fernando. -- 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] column default dependant on another columns value
> -Mensaje original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] En nombre de Tom Lane
> Enviado el: Martes, 01 de Julio de 2008 19:24
> Para: Fernando Hevia
> CC: 'Richard Broersma'; pgsql-sql@postgresql.org
> Asunto: Re: [SQL] column default dependant on another columns value
>
> "Fernando Hevia" <[EMAIL PROTECTED]> writes:
> > Anyway, the rule didn't work. Got "an infinite recursion
> error" when
> > inserting on the table.
> > Can't figure out where the recursion is
>
> You didn't show us the rule, but I imagine that you think the
> WHERE clause is applied while expanding the rule. It's not,
> it can only suppress rows at run-time; and what you've got is
> infinite macro expansion recursion.
>
I see. In that case rules do not serve this particular purpose.
It seems a trigger should be the tool for solving this.
Just to confirm, this is my test case:
create table table1 (
column1 text,
seconds integer,
minutes integer );
CREATE RULE "my_rule" AS ON
INSERT TO table1
WHERE minutes is null
DO INSTEAD
INSERT INTO table1 (column1, seconds, minutes) VALUES(new.column1,
new.seconds, new.seconds/60);
insert into table1 values ('a', 60); --- Here the rule should kick
in right?
insert into table1 values ('b', 120, NULL); --- Rule should kick in too.
insert into table1 values ('c', 180, 3); --- the rule should not apply
since minutes is not null.
Of course, all three of them throw the recursion error.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Strange query duration
Hi list, I just enabled log duration in a 8.3.1 database and got puzzling information. I have a daemon shell-script run every 10 seconds the following: psql -c "select f_tasador();" The 'f_tasador' procedure is quite fast. As per log output I can see the procedure completes its execution within one second. Nevertheless in the LOG duration entry it shows a statement duration of over 36 secs. ¿What is going on? ¿Where come those 36 seconds from? Regards, Fernando --- Postgres Log extract --- 2008-07-22 15:52:37 ART|postgres| LOG: statement: select f_tasador(); 2008-07-22 15:52:37 ART|postgres| NOTICE: [357645] Billable account found 2008-07-22 15:52:37 ART|postgres| NOTICE: [357645] Destination 3514601344 not billable 2008-07-22 15:52:37 ART|postgres| NOTICE: [357645] Destination 3514719096 not billable 2008-07-22 15:52:37 ART|postgres| NOTICE: [357645] Destination 3576421309 not billable 2008-07-22 15:52:37 ART|postgres| NOTICE: [357645] Destination 3514601344 not billable 2008-07-22 15:52:37 ART|postgres| NOTICE: [357645] Destination 3514601344 not billable 2008-07-22 15:52:37 ART|postgres| NOTICE: [357645] Destination 3514601344 not billable 2008-07-22 15:52:37 ART|postgres| NOTICE: [3576450002] No active billable account found 2008-07-22 15:52:37 ART|postgres| NOTICE: [3576450008] Billable account found 2008-07-22 15:52:37 ART|postgres| NOTICE: [3576450008] Destination 3514601344 not billable 2008-07-22 15:52:37 ART|postgres| NOTICE: [3576450008] Destination 3514601344 not billable 2008-07-22 15:52:37 ART|postgres| NOTICE: [3576450008] Destination 3514601344 not billable 2008-07-22 15:52:37 ART|postgres| NOTICE: [3576450008] Destination 3514601344 not billable 2008-07-22 15:52:37 ART|postgres| NOTICE: [3576450008] Destination 3514601344 not billable 2008-07-22 15:52:37 ART|postgres| NOTICE: [3576450008] Destination 3514601344 not billable 2008-07-22 15:52:37 ART|postgres| NOTICE: [3576450009] Billable account found 2008-07-22 15:52:37 ART|postgres| NOTICE: [3576450009] Destination 3516009059 not billable 2008-07-22 15:52:37 ART|postgres| LOG: duration: 38.154 ms 2008-07-22 15:52:47 ART|postgres| LOG: statement: select f_tasador(); 2008-07-22 15:52:47 ART|postgres| NOTICE: [357645] Billable account found 2008-07-22 15:52:47 ART|postgres| NOTICE: [357645] Destination 3514601344 not billable 2008-07-22 15:52:47 ART|postgres| NOTICE: [357645] Destination 3514719096 not billable 2008-07-22 15:52:47 ART|postgres| NOTICE: [357645] Destination 3576421309 not billable 2008-07-22 15:52:47 ART|postgres| NOTICE: [357645] Destination 3514601344 not billable 2008-07-22 15:52:47 ART|postgres| NOTICE: [357645] Destination 3514601344 not billable 2008-07-22 15:52:47 ART|postgres| NOTICE: [357645] Destination 3514601344 not billable 2008-07-22 15:52:47 ART|postgres| NOTICE: [3576450002] No active billable account found 2008-07-22 15:52:47 ART|postgres| NOTICE: [3576450008] Billable account found 2008-07-22 15:52:47 ART|postgres| NOTICE: [3576450008] Destination 3514601344 not billable 2008-07-22 15:52:47 ART|postgres| NOTICE: [3576450008] Destination 3514601344 not billable 2008-07-22 15:52:47 ART|postgres| NOTICE: [3576450008] Destination 3514601344 not billable 2008-07-22 15:52:47 ART|postgres| NOTICE: [3576450008] Destination 3514601344 not billable 2008-07-22 15:52:47 ART|postgres| NOTICE: [3576450008] Destination 3514601344 not billable 2008-07-22 15:52:47 ART|postgres| NOTICE: [3576450008] Destination 3514601344 not billable 2008-07-22 15:52:47 ART|postgres| NOTICE: [3576450009] Billable account found 2008-07-22 15:52:47 ART|postgres| NOTICE: [3576450009] Destination 3516009059 not billable 2008-07-22 15:52:47 ART|postgres| LOG: duration: 36.781 ms -- 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] Strange query duration
> De: Richard Broersma [mailto:[EMAIL PROTECTED] > Enviado el: Martes, 22 de Julio de 2008 17:19 > > > 2008-07-22 15:52:37 ART|postgres| LOG: duration: 38.154 ms > > :o) You might be encountering a bit of parallax. This shows > both 38 and 36 *milliseconds*. > That's embarrasing... I mistook the decimal punctuation symbol (in my native Spanish it's the coma) Good to know it performs as fast as expected though. :) 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] Cursor
> De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>] En nombre de Xavier Bermeo > Enviado el: Sábado, 16 de Agosto de 2008 14:54 > Para: pgsql-sql@postgresql.org > Asunto: [SQL] Cursor > > Hi, guys... > I have problems with cursosrs. > Anyone have an example complete the how load and read each position of a cursor? > I wait your answer > Thanks...guys Hi Xavier. This is a very simple example of a function with an implicit cursor. Regards, Fernando. CREATE OR REPLACE FUNCTION f_cursor() RETURNS void AS $BODY$ DECLARE idx INTEGER; a mytable.col1%TYPE; b mytable.col2%TYPE; c mytable.col3%TYPE; d mytable.col4%TYPE; BEGIN idx :=0; FOR a, b, c, d IN SELECT col1, col2, col3, col4 FROM mytable ORDER BY col1 ; LOOP -- Comment: Every iteration in loop will read a row from the cursor idx := idx + 1; raise notice 'Row %: [%, %, %, %]', idx, a, b, c, d; END LOOP; -- Comment: all rows have been read END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; -- 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] MAY I HAVE YOUR ASSISTANCE
> -Mensaje original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] En nombre de Gary Chambers
> Enviado el: Lunes, 01 de Septiembre de 2008 11:31
> Para: D'Arcy J.M. Cain
> CC: [EMAIL PROTECTED]; pgsql-sql@postgresql.org
> Asunto: Re: [SQL] MAY I HAVE YOUR ASSISTANCE
>
> >> I have one problem with the user table. I want to hide the
> password for the users.
>
> Here's what I did, which requires using the contrib/pgcrypto
> extension:
>
> CREATE FUNCTION encryptpw() RETURNS TRIGGER AS $encryptpw$ BEGIN
> NEW.password = CRYPT(NEW.password, GEN_SALT('md5'));
> RETURN NEW;
> END;
> $encryptpw$
>
> CREATE TRIGGER trg_encryptpw BEFORE INSERT OR UPDATE ON
> assignees FOR EACH ROW EXECUTE PROCEDURE encryptpw();
>
> Comments, suggestions, criticisms?
>
> -- Gary Chambers
>
The weakness of this solution is that your password might be send in the
clear through the network as the encription ocurrs in the database. I
suggest the encryption be enforced at the application or secure the
connection with ssl.
--
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] Syntax help please
You seem to be missing a ';' in this line: v_from := c_from ; v_where := p_where<--- missing ; here v_stmt := c_select || v_from || v_where; Regards, Fernando De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] En nombre de Ruben Gouveia Enviado el: Jueves, 04 de Septiembre de 2008 16:37 Para: pgsql-sql@postgresql.org Asunto: [SQL] Syntax help please I can't for the life of me figure out what's wrong with this syntax. I get the following error when i try and create this function. ERROR: syntax error at or near "$2" at character 15 QUERY: SELECT $1 $2 := $3 || $4 || $5 CONTEXT: SQL statement in PL/PgSQL function "fcn_gen_statement" near line 24 here's what i am trying to create: CREATE OR REPLACE FUNCTION fcn_gen_statement(p_type varchar, p_where varchar, p_newonly numeric) RETURNS varchar AS $$ DECLARE c_select varchar(64) := 'select count(distinct m.id) '; c_from varchar(64) := 'from job m '; c_newonly_from varchar(128) := 'from (select id, min(date_created) as date_created '|| 'from hr '|| 'group_by id) m '; v_from varchar(512); v_where varchar(512); v_stmt varchar(2048); BEGIN if p_newonly = 1 then v_from := c_newonly_from; else v_from := c_from; end if; if upper(p_type) = 'NEW' then v_stmt := c_select || v_from || p_where; elsif upper(p_type) = 'OLD' then v_from := c_from ; v_where := p_where v_stmt := c_select || v_from || v_where; elsif upper(p_type) = 'LAST_JOB' then v_from := v_from || ', (select distinct job_id ' || 'from job_log' || 'where status = 10) d '; v_where := p_where || 'and m.id = d.job_id '; v_stmt := c_select || v_from || v_where; elsif upper(p_type) = 'NEW_JOB' then v_from := v_from || ', (select distinct job_id ' || 'from job_log' || 'where status = 12) d '; v_where := p_where || 'and m.id = d.job_id '; v_stmt := c_select || v_from || v_where; end if; return (v_stmt); END; $$ LANGUAGE plpgsql; -- 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] Pls Hlp: SQL Problem
> -Mensaje original- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] En nombre de Scott Marlowe > > On Fri, Sep 12, 2008 at 12:50 PM, Hengky Lie > <[EMAIL PROTECTED]> wrote: > > Dear Richard, > > > > Put parens around the whole thing, like: > > (tbltransaksi.hargapokok * tbltransaksi.keluar)::numeric(2,0) AS modal > Consider that if you are NOT going to use the decimals you should really use integer or bigint datatypes. The numeric type compute much slower than integer datatypes. Regards, Fernando -- 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] Pls Hlp: SQL Problem
> -Mensaje original- > De: Scott Marlowe [mailto:[EMAIL PROTECTED] > > > > Consider that if you are NOT going to use the decimals you should > > really use integer or bigint datatypes. The numeric type > compute much > > slower than integer datatypes. > > Note that if you're just converting the output the cost is > minimal compared to if you're doing all your math in numeric. > It's when you force math to happen that numeric is slower, > but numeric's gotten a lot of tuning in the last few years > and it's withing a few percentage > of integer for most measurements. Definitely not twice as slow or > anything like they once were. > Well, in that case the manual should be revised. 8.1.2. Arbitrary Precision Numbers The type numeric can store numbers with up to 1000 digits of precision and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where exactness is required. However, arithmetic on numeric values is __very slow__ compared to the integer types, or to the floating-point types described in the next section. Ref: http://www.postgresql.org/docs/current/static/datatype-numeric.html The explicit "very slow" assertion scared me quite enough so to avoid numeric types where possible. Regards, Fernando. -- 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] For each key, find row with highest value of other field
> Raj Mathur wrote: > > I have some data of the form: > > Key | Date | Value > A | 2008-05-01 | foo* > A | 2008-04-01 | bar > A | 2008-03-01 | foo* > B | 2008-03-04 | baz > B | 2008-02-04 | bar > C | 2008-06-03 | foo* > C | 2008-04-04 | baz > C | 2008-03-04 | bar > > Is there any way to select only the rows marked with a (*) > out of these without doing a join? I.e. I wish to find the > row with the highest Date for each Key and use the Value from that. > This should do it: Select value from table a where date = (select max(b.date) from table b where b.key = a.key) q; Regards, Fernando. -- 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] SELECT multiple MAX(id)s ?
> -Mensaje original- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] En nombre de Aarni Ruuhimäki > Enviado el: Viernes, 10 de Octubre de 2008 07:56 > Para: pgsql-sql@postgresql.org > Asunto: [SQL] SELECT multiple MAX(id)s ? > > Hello list, > > table diary_entry > > entry_id SERIAL PK > d_entry_date_time timestamp without time zone > d_entry_company_id integer d_entry_location_id integer > d_entry_shift_id integer d_user_id integer d_entry_header text ... > > Get the last entries from companies and their locations? > > The last, i.e. the biggest entry_id holds also the latest > date value within one company and its locations. One can not > add an entry before the previuos one is 'closed'. Names for > the companies, their different locations, or outlets if you > like, users and shifts are stored in company, location, user > and shift tables respectively. > > Again something I could do with a bunch of JOIN queries and > loops + more LEFT JOIN queries within the output loops, but > could this be done in a one single clever (sub select?) query? > > Output (php) should be something like: > > Date | User | Shift | Company | Location > - > > 02.10.2008 | Bobby | Nightshift 1 | Company 1 | Location X > 04.10.2008 | Brian | Dayshift 2 | Company 1 | Location Y > 09.10.2008 | Jill | Dayshift 1 | Company 2 | Location A > 05.10.2008 | Jane | Dayshift 1 | Company 2 | Location B > 07.10.2008 | Frank | Dayshift 2 | Company 2 | Location C ... > > Someone please give me a start kick? > > TIA and have a nice weekend too! > > -- > Aarni > > Burglars usually come in through your windows. > Aarni, you should take a look at aggregate functions. Anyway, I think this is what you are asking for: select max(d.d_entry_date_time) as Date, u.name, s.shift, c.name, l.location_name from diary_entry d, company c, location l, user u, shift s where d.d_entry_company_id = c.company_id and d.d_entry_location_id = l.location_id and d.d_user_id = u.user_id and d.d_entry_shift_id = s.shift_id group by u.name, s.shift, c.name, l.location_name order by d.d_entry_date_time Cheers. -- 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] Display message to user
Hi Bart, If you are using psql then you can get rid of those messages by entering on the psql prompt: \set verbose TERSE If you are using pgAdmin, sadly there is currently no way for this application to filter context messages. Regards, Fernando _ De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] En nombre de Bart van Houdt Enviado el: Martes, 04 de Noviembre de 2008 10:32 Para: pgsql-sql@postgresql.org Asunto: [SQL] Display message to user Hi all, I am a Postgres-newbie and working on porting our code from Oracle to Postgres. Oracle has a nice package procedure (dbms_output.put_line) to display a message in SQL*Plus, which can display a message to the user. I use this a lot, to notify users of the progress being made during the execution of a script. Is there a way to do this with Postgres as well? I have tried using 'RAISE NOTICE', but using this some extra lines are printed on screen. Those extra lines mess up the screen and will confuse the users. The extra lines shown are: CONTEXT: SQL statement "SELECT migration_pkg.time_migration( $1 , $2 )" PL/pgSQL function "check_migration" line 34 at PERFORM Any help would be appreciated, Bart van Houdt Syfact International B.V. Database developer
Re: [SQL] Subsorting GROUP BY data
> -Mensaje original- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] En nombre de Johnson, > Michael L. > Enviado el: Lunes, 10 de Noviembre de 2008 12:57 > Para: pgsql-sql@postgresql.org > Asunto: [SQL] Subsorting GROUP BY data > > Given the following table: > > ID | Cat | Num > |---|-- > Z | A | 0 > Y | A | 1 > X | A | 2 > W | B | 0 > V | B | 1 > U | B | 2 > T | C | 0 > S | C | 1 > R | C | 2 > > I want to do this: Group the items by the cat field. Then > select the ID where the num is the highest in the group; so > it should return something like: > > Cat | ID | Num > -|--|-- > A | X | 2 > B | U | 2 > C | R | 2 > > > Using SQL like this, I can get the category and the highest # in the > category: > > SELECT cat, MAX(num) FROM my_table GROUP_BY cat; > > But if I add the "id" column, of course it doesn't work, > since it's not in an aggregate function or in the GROUP_BY > clause. So I found a post at > http://archives.postgresql.org/pgsql-hackers/2006-03/msg01324.php > which describes how to add a "FIRST" and "LAST" aggregate > function to PGSQL. However, first and last don't seem to > help unless you are able to "subsort" the grouping by the # > (ie, group by cat, then subsort on num, and select the "last" > one of the group). > I wonder if this suites you: SELECT sub.cat, t.id, sub.Num FROM my_table t, ( SELECT cat, MAX(num) as Num FROM my_table GROUP_BY cat ) sub WHERE t.cat = sub.cat AND t.Num = sub.Num ORDER BY t.cat; Regards, Fernando. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Object create date
Hi list, I'm having a hard time trying to find out if the latest patches have been applied to my application (uses lots of pgplsql functions). Does Postgres store creation date and/or modification date for tables, functions and other objects? It would help me a lot if I could query each object when it was created. Is this information available on 8.3? Where should I look? Thanks, Fernando
Re: [SQL] Object create date
> -Mensaje original- > De: pgsql-sql-ow...@postgresql.org > [mailto:pgsql-sql-ow...@postgresql.org] En nombre de Scott Marlowe > > On Mon, Dec 29, 2008 at 11:23 AM, Fernando Hevia > wrote: > > Hi list, > > > > I'm having a hard time trying to find out if the latest > patches have > > been applied to my application (uses lots of pgplsql functions). > > Does Postgres store creation date and/or modification date > for tables, > > functions and other objects? > > It would help me a lot if I could query each object when it was > > created. Is this information available on 8.3? Where should I look? > > PostreSQL doesn't track this kind of thing for you. Too bad it doesn't. I think it would be quite useful that the database saved the creation time of at least some objects. > An easy method to implement yourself is to create a table to track > such changes, and add a line to insert data into that table. > > create table change_track (version numeric(12,2) primary key, > title text, summary text); > > Then in a script, always update like so: > > begin; > insert into change_track(10.2, 'plpgsql - add / remove','New > plpgsql stored procedure to add and remove users. > adduser(uid,''username''), deluser(uid)'); > > create function > > commit; > Although it's not a solution for an already messed-up database it is an interesting solution to consider for the future. Thanks Scott. Regards, Fernando. -- 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] Object create date
Thanks Pavlov for your response. > -Mensaje original- > De: George Pavlov [mailto:gpav...@mynewplace.com] > wrote: > > Hi list, > > > > I'm having a hard time trying to find out if the latest > patches have > > been applied to my application (uses lots of pgplsql functions). > > Does Postgres store creation date and/or modification date > for tables, > > functions and other objects? > > It would help me a lot if I could query each object when it was > > created. Is this information available on 8.3? Where should I look? > > 1. not exactly what you were looking for, but i answer this > partially by putting a commented-out CVS expansion tag (e.g. > $Id:) in the body of the function so that it gets into the > catalog and can be searched: > > CREATE OR REPLACE FUNCTION foo () > RETURNS void AS > $BODY$ > -- $Id: foo.sql,v 1.6 2008/12/23 00:06:52 gpavlov Exp $ > BEGIN > ... > I am already doing this. Sadly I've found it to be very fragile in face of a careless programmer who forgets to update the tags. Myself being the prime suspect. :) > and query it by something like this: > > select > routine_name, > substring(routine_definition from E'%#\042-- #\044Id: % > Exp #\044#\042%' for '#') as cvs_id > from information_schema.routines > ; This query is very helpful. > > 2. you can also make some inference about the relative timing > of object creation based on the OIDs (query > pg_catalog.pg_proc rather than information_schema.routines > for proc OIDs). > I am not sure this would be helpful since different databases are involved (same product on several installations). I think that with the above query I will be able to sort things out. Thank you. Regards, Fernando. -- 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] Object create date
> -Mensaje original- > De: Alvaro Herrera [mailto:alvhe...@commandprompt.com] > Enviado el: Lunes, 29 de Diciembre de 2008 19:39 > Para: Fernando Hevia > CC: 'George Pavlov'; pgsql-sql@postgresql.org > Asunto: Re: [SQL] Object create date > > Fernando Hevia escribió: > > Thanks Pavlov for your response. > > > > CREATE OR REPLACE FUNCTION foo () > > > RETURNS void AS > > > $BODY$ > > > -- $Id: foo.sql,v 1.6 2008/12/23 00:06:52 gpavlov Exp $ > > > BEGIN > > > ... > > > > > > > I am already doing this. Sadly I've found it to be very fragile in > > face of a careless programmer who forgets to update the > tags. Myself > > being the prime suspect. :) > > You don't update the tags. They are updated automatically by > CVS (or Subversion, whatever you use) > Hmm, I'm using source-safe. Just went through the manual and it does support auto expandable tags, something which I hadn't used before. Thanks for the tip. -- 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] Best practices for geo-spatial city name searches?
> -Mensaje original- > From: Mark Stosberg > > Hello, > > I use PostgreSQL and the "cube" type to perform geo-spatial > zipcode proximity searches. I'm wondering about the best > practices also supporting a geo-spatial distance search based > on a city name rather than zipcode. > > In our original data model, we used a 'zipcodes' table, with > the zipcode as the primary key. This can of course contain a > "City Name" column, but there is a problem with this, > illustrated a "Nome, Alaska" case. Nome's zipcode is 99762. > It maps to multiple cities including Diomede, Alaska and Nome, Alaska. > > In the data model described, only the "Diomede" row is > imported, and the other rows, including the "Nome, Alaska" > row are dropped. So if you try to search for Nome, Alaska, > you won't find anything. > > One solution would be to have a "cities" table, with the > city/state as the primary key, and a zipcode as an additional > column. Then, by joining on the zipcodes table, the > coordinates for a city could be found. > > Is there any other way I should be considering data modelling > to support searches on zipcodes and cities? > You absolutely need zipcode as a primary key? If you must enforce non duplicate entries use country + state + county + city_name instead. You might still need to throw zipcode into the PK for certain cities (worldwide). Otherwise, latitud & longitude provide a better natural key, or simply use a non data related sequential bigint. Regards. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Complex query
Hey everybody !!! I am new on this list !!! I have a little problem . I try this on my system: "select n_lote from pedidos except select rp.n_lote from relpedidos rp, relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having sum(rp.cantidad)=sum(rf.cantidad)" I get this result: ERROR: rewrite: comparision of 2 aggregate columns not supported but if a try this one: "select rp.n_lote from relpedidos rp, relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having sum(rp.cantidad)=sum(rf.cantidad)" It's OK !! What's up??? Do you think i found a bug ??? Do exist some limitation like this in subqueries?? (Perhaps Postgres don't accept using aggregates in subqueries ???) I tried this too: "select n_lote from pedidos where n_lote not in (select rp.n_lote from relpedidos rp, relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having sum(rp.cantidad)=sum(rf.cantidad))" but the result was the same ! And i get the same error message (or similar) when i try other variations. Thanks !!! Fer -- * ** ** ** * **** * * ** ** ** * *** * * * * *** * *** * ** *** * * * * * ** ** * * * (*) SymeX ==> http://www.lantik.com (*) Web en http://www.arrakis.es/~txino (*) Informate sobre LINUX en http://www.linux.org
[SQL] Problems with complex queries ...
Hey everybody !!! I am new on this list !!! I have a little problem . I try this on my system: (Postgres 6.5.2, Linux) "select n_lote from pedidos except select rp.n_lote from relpedidos rp, relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having sum(rp.cantidad)=sum(rf.cantidad)" and I get this result: ERROR: rewrite: comparision of 2 aggregate columns not supported but if I try this one: "select rp.n_lote from relpedidos rp, relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having sum(rp.cantidad)=sum(rf.cantidad)" It's OK !! What's up??? Do you think i found a bug ??? Do exists some limitation like this in subqueries?? (Perhaps Postgres don't accept using aggregates in subqueries ???) I tried this too: "select n_lote from pedidos where n_lote not in (select rp.n_lote from relpedidos rp, relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having sum(rp.cantidad)=sum(rf.cantidad))" but the result was the same ! And i get the same error message (or similar) when i try other variations. Thanks !!! Fer -- * ** ** ** * **** * * ** ** ** * *** * * * * *** * *** * ** *** * * * * * ** ** * * * (*) SymeX ==> http://www.lantik.com (*) Web en http://www.arrakis.es/~txino (*) Informate sobre LINUX en http://www.linux.org
[SQL] Select very slow...
Table 1 create table person ( doc text primary key, etc city text ); 9 rows create table sales ( doc text, etc . ); 30 rows select p.city,count(*) from sales s, person p where s.doc = p.doc group by p.city; Anyone help-me? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] C Functions
#include
#include
char *fernando(char *texto)
{
char *resultp = palloc(strlen(texto)+5);
*resultp = *texto;
strcat(resultp," mais");
return resultp;
}
gcc -shared fernando.c -o fernando.so
CREATE FUNCTION fernando (bpchar) RETURNS bpchar
AS '/u/src/tef/fernando.so' LANGUAGE 'c';
CREATE
SELECT fernando ('Teste');
ERROR: Memory exhausted in AllocSetAlloc(287341377)
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
