[SQL] \COPY in psql using \e

file in vi in a xterm and execute it from another xterm. Anything cleaner? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Postgres function for full text search

ave to adjust them to your environment. The code could be released in GPL or FreeBSD license. I'd be happy if someone could guide me in the process of successfully donating this code to the community. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (

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

s out to be: SPI_prepare("SELECT $1 + $2", 2, ...); and this is going to be called for every loop. while I thought the SQL engine and plpgsql interpreter were nearer so that the interpreter could push directly in the SQL engine the values of a. Am I getting nearer? -- Ivan Sergio Borgonovo ht

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

s use internal query based API. > this query is little but different, than you original request, but > it could work for you. Yep... making clear a is an int simplify the problem quite a lot. But you couldn't use generate_series if a was not an int. thanks -- Ivan Sergio Borgonovo http://w

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

t the simplest version is still leading. Just when the interval is in [1,10] the plpgsql version and the simplest one becomes comparable. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] short-cutting if sum()>constant

ir text representation. It would be nice if someone that know plpgsql internals explain where the cost comes from. > I thing, so there are other trick, I am not sure if it is faster. > You can create own aggregate. In state function you can calculate > and check state value. If it

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

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

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

On Wed, 23 Dec 2009 00:00:31 +0100 Ivan Sergio Borgonovo wrote: > On Tue, 22 Dec 2009 20:47:18 +0100 > Pavel Stehule wrote: > > > Hello > > > > I found one ugly trick. You can multiply lines and SUM > cons > > could be replaced limit clause: > >

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

from tano(10); b 10 (1 row) Time: 0.187 ms I run both several times to avoid simple caching issues... anyway I didn't really run a serious benchmark, but results were always in the same order of magnitude. I hope I didn't make any mistake. -- Ivan Sergio Bor

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

gsql function using cursors and then switch to WITH when I'll move to 8.4. -- Ivan Sergio Borgonovo http://www.webthatworks.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] short-cutting if sum()>constant

Hi, I'd like to know if select sum(qty) from t where status=37; is > constant. qty is always >0. Is there a way to skip examining further rows and return a result ASAP? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgre

Re: [SQL] get distinct + group by then filter

On Fri, 18 Dec 2009 13:20:00 +0100 Filip Rembiałkowski wrote: > 2009/12/17 Ivan Sergio Borgonovo > > > I've a web application and I'm trying to do some reporting on > > affiliate commission > > > > create table tracky_hit ( > > hitid serial

[SQL] get distinct + group by then filter

interval '1 months' + interval '1 days') order by esid, track_time ) and th.aid='someaid' and th.track_time between ('2009-12-01'::timestamp) and ('2009-12-01'::timestamp + interval '1 months

Re: [SQL] Random Unique Id

to share it so here it is: http://www.webthatworks.it/d1/node/page/pseudo_random_sequences_postgresql Adapted from Daniel Verite suggestion. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your sub

Re: [SQL] Obtaining a limited number of records from a long query

t and Postgresql don't behave that way or it happens just by chance. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Obtaining a limited number of records from a long query

just don't care) you may incur in unexpected results. A query without an order by is not granted to return the result in the same order. If you can (same session) you may use cursors. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@post

[SQL] 2 left join taking in too many records, 1 join and 1 left join too few was: join

ke it work anyway as expected. I still don't like the overall solution, but at least it makes a more reasonable starting point to clean some mess and having a list of IsPromo around ready will come handy in other places. On Tue, 17 Mar 2009 22:40:08 +0100 Ivan Sergio Borgonovo wrote: > I&#x

[SQL] join

, p.OnListPrice, p.Vendible, p.OnStock, ip.Price, i.valIva, b.Name, i.BrandID, i.Authors, i.dataPub, s.FamID, st.Name, b.Delivery -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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]

don't cross post and this is supposed to be an English list (am I right?). Most of the people here will see your post as noise here. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] row not deleted but updated (trigger?)

On Fri, 27 Feb 2009 12:56:06 +0100 Ivan Sergio Borgonovo wrote: > I've: > > create or replace function FT1IDX_catalog_brands_update() returns > trigger as $$ > begin > if(TG_OP='DELETE') then > update catalog_items set > FT1IDX=GetFTIDX('pg_c

[SQL] row not deleted but updated (trigger?)

elete from catalog_brands where brandid=1234; no row get deleted and no error get reported. what did I miss? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] dynamic OUT parameters?

ong > with that. I've followed this thread with interest. I'm starting to manage larger and larger code base of plpsql. What I find hard to achieve is code reuse and implication of some techniques to performances. I didn't see many examples that exploit cursors aroun

Re: [SQL] some howto/theory book/tutorial on practical problem solving in SQL

On Tue, 13 Jan 2009 22:18:32 -0800 Erik Jones wrote: > > On Jan 11, 2009, at 8:32 AM, Ivan Sergio Borgonovo wrote: > > > I'm looking to some book/tutorial/sample code that will teach me > > how to use SQL to solve some standard problem that goes a bit > > beyo

Re: [SQL] some howto/theory book/tutorial on practical problem solving in SQL

On Sun, 11 Jan 2009 11:19:19 -0700 "Scott Marlowe" wrote: > On Sun, Jan 11, 2009 at 9:32 AM, Ivan Sergio Borgonovo > wrote: > > My current problem is how to manage discounts in SQL, inside > > transactions. Specifically how to "delete" promotions if they a

[SQL] some howto/theory book/tutorial on practical problem solving in SQL

. I skimmed through Celko books and at the moment they seems the nearest thing to what I'd like to learn even if too much "theoretical" at the moment. O'Reilly "SQL cookbook" is another example of the kind of stuff I'm looking for... but the examples are more lik

Re: [SQL] Dynamic SQL Syntax help please

) syntax with pgplsql syntax EXECUTE v_stmt INTO v_total_pop; http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -- Ivan Sergio Borgonovo http://www.webthatworks.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] integrity check and visibility was: COPY equivalent for updates

I'll have to write an update for each tableN anyway... so there shouldn't be too much difference between the UPDATE and the DELETE/INSERT approach. What about the effect of a longer transaction compared to a list of updates on speed? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.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] COPY equivalent for updates

ents. Supposing I could neglect the awk execution time, will COPY + UPDATE be faster than executing a list of UPDATE? Considering I've to deal with a where clauses anyway... when (and if) should I create an index on the id of temp_t1? t1 will contain 700-1M records while I may update a ma

[SQL] selecting N record for each group

#x27;m not sure it could make any better compared to plain sql. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] function returning result set of varying column

ould work. SQL functions are less flexible than pg/plsql but maybe you can live with it. SQL functions still support conditionals etc... you'd have to see if what SQL functions offer is enough for your need. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql m

Re: [SQL] function returning result set of varying column

lly remember how it works. If you post your tentative sql it could give us more clue. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] function returning result set of varying column

> CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS > > $body$ > > DECLARE > > rec record; > > BEGIN > > FOR rec IN ( > > SELECT * FROM sometable) > > LOOP > > RETURN NEXT rec; > > END LOOP; > > RETURN; > >

Re: [SQL] Protection from SQL injection

On Sun, 27 Apr 2008 11:55:18 -0400 Joe <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo wrote: > > It'd be nice to have a wrapper that let you write prepared > > statements this way: > > > > "select a.id, b.name from a join b on a.id=b.id where >

Re: [SQL] Protection from SQL injection

On Sun, 27 Apr 2008 12:38:48 +0200 Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > Once you've developers that are so patient to write stuff like: > > "select a.id, b.name from a join b on b.id=a.id where > a.status='pending' and b.id>7 and b.sta

Re: [SQL] Protection from SQL injection

you want to make your dev life a hell... it is not going to solve the SQL injection problem. "mixed" statements that use external input and static input are quite common and writing them avoiding literals may be a pain that your dev won't be willing to suffer. Queued statements in o

Re: [SQL] Protection from SQL injection

tying the hands of the > programmers. > But I've always had the luxury of working with developers who liked > me as a DBA and were willing to do things my way, as far as the DB > was concerned anyway... what if you're the DBA and the dev and you don't t

[SQL] advocacy: case studies

e-engineered by XDI," the company says in a newsletter. "All of the front-end PostgreSQL interfaces have been maintained intact and the back-end execution engine has been transformed to leverage the shared-nothing parallel cluster environment with FPGA acceleration." ?

Re: [SQL] update with join

On Wed, 2 Apr 2008 23:54:18 -0300 "Osvaldo Kussama" <[EMAIL PROTECTED]> wrote: > 2008/4/2, Ivan Sergio Borgonovo <[EMAIL PROTECTED]>: > > I've > > > > create table types( > > typeid int, > > special boolean not null > >

[SQL] update with join

understand why but I can't rewrite it to make it work. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Export Access 97 to PostgreSQL

t could be the second thing to look at. > Here is a sample of my file (fields seperated with a tab) > 1360077,00 848351,00 2,00 2,00 4,00 "Trentino-alto adige" > 1842400,00 1132202,00 3,00 3,00 5,00 "Veneto" > 7859928000,00 746082,90 4,00 4,00 6,00 "

Re: [SQL] plpgsql vs. SQL in stored procedures

On Wed, 31 Mar 2004 03:31:01 -0500 Christopher Browne <[EMAIL PROTECTED]> wrote: > The forthcoming support for recursive queries using a WITH clause > might provide, after a fashion, a way to declare variables. I think I'll have to work with pg 7.3 Does this translate to: you won't be able to use

[SQL] plpgsql vs. SQL in stored procedures

Is there a way to declare variables and use IF in plain SQL, not in plpgsql inside stored procedures? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster