Re: [SQL] Alternative to INTERSECT

2007-07-31 Thread Josh Trutwin 
On Tue, 31 Jul 2007 17:30:51 +
Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote:

> Hi all. I have the following schema:
> 
> CREATE TABLE test (
> id integer NOT NULL,
> field character varying NOT NULL,
> value character varying NOT NULL
> );
> 
> ALTER TABLE ONLY test
> ADD CONSTRAINT test_id_key UNIQUE (id, field, value);
> 
> CREATE INDEX test_like_idx ON test USING btree (id, field, value 
> varchar_pattern_ops);
> 
> Using INTERSECT I want to retrieve the rows matching (pseudo-code)
> "firstname LIKE ('andrea%' OR 'jose%') AND lastname LIKE 'kro%'"

Why not:

WHERE (t.field = lastname AND t.value LIKE 'kro%')
   OR (t.field = firsname AND (
   t.value LIKE 'jose%' OR t.value LIKE 'andrea%')
   )

Not tested.  If you're having performance problems is probably less
like that the INTERSECT is the problem with all those LIKE's in
there?  Is t.value indexed?

Josh

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Accessing field of OLD in trigger

2007-10-12 Thread Josh Trutwin 
On Fri, 12 Oct 2007 12:00:55 +0200 (CEST)
Daniel Drotos <[EMAIL PROTECTED]> wrote:

> Hi,
> 
> 
> I'm working on a row level plpgsql trigger running after delete,
> using a 8.0.3 server. It gets a parameter which is a field name of
> the OLD record. How can that field be accessed?
> 
> I'd like to do something like:
> 
> for recvar in 'select OLD.'||quote_ident(TG_ARGV[0])...

I THINK you are out of luck here.  I hear it's possible to do but in
one of the other PL languages say pl/tcl, though I can't seem to find
an example

Josh

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Table to Excel

2009-03-26 Thread Josh Trutwin 
On Thu, 26 Mar 2009 08:04:25 +0100
Pavel Stehule  wrote:

> hello
> 
> use csv format - excel have to read this format without any format
> 
> COPY tablename TO '...' CSV;

Another possibility that I use often is to set the output to HTML
mode using:

\H

Then set to send query results to a file:

\o myresults.html

Then run a single query:

SELECT col1, col2 FROM foo WHERE bar = '1' ORDER BY baz;

\q

Now you can open your .html file directly into Excel since it's just
a big html table.

Josh

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql