[SQL] Indexing a field of type point
Hi, I realise this isn't strictly an SQL question, but I figured this list might be better suited than the general one. Please let me know if not. I have a table containing a field named location, of type point, i.e. a position in two dimensions. The table has several million records in, and I need to extract those records whose location value is contained within a certain bounding box. To do this efficiently (rather than as a linear scan), I would like to create an index over this field. However, using GIST under Postgresql 8.2.4 I can't do this: test=# create index points_location_index on points using gist (location); ERROR: data type point has no default operator class for access method "gist" HINT: You must specify an operator class for the index or define a default operator class for the data type. Looking through the available classes, there are none defined for points :-(. I have seen a post saying that one could use create index points_location_index on points using gist (location box_ops); but that comes back with the error that (rightly) box_ops doesn't have operators for data of type point. Is anyone aware of a way of creating a suitable index? I am aware of PostGIS, but would prefer not to have to rework a whole load of code to use the different geometrical field types it provides. Thanks for any help! David. -- David Cottingham Computer Laboratory, University of Cambridge http://www.cl.cam.ac.uk/users/dnc25/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Indexing a field of type point
David Cottingham <[EMAIL PROTECTED]> writes: > I have a table containing a field named location, of type point, i.e. a > position in two dimensions. The table has several million records in, and I > need to extract those records whose location value is contained within a > certain bounding box. Given the standard opclasses, your best bet is to convert the point into a zero-volume box or circle, eg create index i on t using gist (circle(pointcol,0)) and then express queries as "circle(pointcol,0) overlaps target-box". PostGIS might have something more nicely adapted ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] PG won't use index on ORDER BY
Hi all! I have problems getting PG to use an index when sorting. I have a simple table create table person( id serial primary key, firstname varchar, lastname varchar ); I create an index: CREATE INDEX person_lowerfullname_idx ON person((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, ''))) varchar_pattern_ops); And this query refuses to use that index: select id from person order by (lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, ''))) ASC limit 1; If I add an index: CREATE INDEX person_lowerfirstname_idx ON person(lower(firstname)); The following query will use that index for sorting and cut-off: select id from person order by (lower(firstname) ) ASC limit 1; Any hints or explaination on why the "concat-index" won't be used? PS: I have tried to issue a "set enable_seqscan to off;" to ensure that it will use an index if one appropriate exists -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] PG won't use index on ORDER BY
"Andreas Joseph Krogh" <[EMAIL PROTECTED]> writes: > I create an index: > CREATE INDEX person_lowerfullname_idx ON > person((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, ''))) > varchar_pattern_ops); Why are you declaring it using the varchar_pattern_ops? The default operator set is the one you want for handling ordering. The pattern_ops operator set is for handling things like x LIKE 'foo%' -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] PG won't use index on ORDER BY
On Thursday 09 August 2007 22:00:54 Gregory Stark wrote: > "Andreas Joseph Krogh" <[EMAIL PROTECTED]> writes: > > I create an index: > > CREATE INDEX person_lowerfullname_idx ON > > person((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, ''))) > > varchar_pattern_ops); > > Why are you declaring it using the varchar_pattern_ops? > > The default operator set is the one you want for handling ordering. The > pattern_ops operator set is for handling things like x LIKE 'foo%' Ooops, just fugured that out. But - it still doesn't use the index if I remove the "varchar_pattern_ops". I solved it by adding a function: CREATE OR REPLACE FUNCTION concat_lower(varchar, varchar) RETURNS varchar AS $$ SELECT lower(coalesce($1, '')) || lower(coalesce($2, '')) $$ LANGUAGE SQL IMMUTABLE; And than creating an index: CREATE INDEX person_lowerfullname_idx ON person(concat_lower(firstname, lastname)); Another question then: Why doesn't "varchar_pattern_ops" handle ordering? This means I need 2 indexes on the columns I want to match with LIKE and ORDER BY. Just doesn't seem right to need 2 "similar" indexes... -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ ---(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] PG won't use index on ORDER BY
On 8/9/07, Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote: > Ooops, just fugured that out. But - it still doesn't use the index if I remove > the "varchar_pattern_ops". Huh? CREATE INDEX person_lowerfullname_idx ON person ((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, ''; EXPLAIN ANALYZE select id from person order by (lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, ''))) ASC limit 1; Limit (cost=0.00..0.08 rows=1 width=68) (actual time=0.030..0.030 rows=0 loops=1) -> Index Scan using person_lowerfullname_idx on person (cost=0.00..62.25 rows=800 width=68) (actual time=0.018..0.018 rows=0 loops=1) Total runtime: 0.318 ms EXPLAIN ANALYZE select id from person where (lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, ''))) like 'A%' order by (lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, ''))) ASC limit 1; Limit (cost=0.01..5.10 rows=1 width=68) (actual time=0.038..0.038 rows=0 loops=1) -> Index Scan using person_lowerfullname_idx on person (cost=0.01..20.34 rows=4 width=68) (actual time=0.032..0.032 rows=0 loops=1) Index Cond: (((lower((COALESCE(firstname, ''::character varying))::text) || lower((COALESCE(lastname, ''::character varying))::text)) >= 'A'::text) AND ((lower((COALESCE(firstname, ''::character varying))::text) || lower((COALESCE(lastname, ''::character varying))::text)) < 'B'::text)) Filter: ((lower((COALESCE(firstname, ''::character varying))::text) || lower((COALESCE(lastname, ''::character varying))::text)) ~~ 'A%'::text) Total runtime: 0.138 ms Works for me. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] PG won't use index on ORDER BY
On Thursday 09 August 2007 22:57:35 Rodrigo De León wrote: > On 8/9/07, Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote: > > Ooops, just fugured that out. But - it still doesn't use the index if I > > remove the "varchar_pattern_ops". > > Huh? > > CREATE INDEX person_lowerfullname_idx > ON person > ((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, ''; > > > EXPLAIN ANALYZE select id from person order by > (lower(COALESCE(firstname, '')) || > lower(COALESCE(lastname, ''))) ASC limit 1; > > Limit (cost=0.00..0.08 rows=1 width=68) (actual time=0.030..0.030 > rows=0 loops=1) > -> Index Scan using person_lowerfullname_idx on person > (cost=0.00..62.25 rows=800 width=68) (actual time=0.018..0.018 rows=0 > loops=1) > Total runtime: 0.318 ms > > > EXPLAIN ANALYZE select id from person > where (lower(COALESCE(firstname, '')) || > lower(COALESCE(lastname, ''))) like 'A%' > order by (lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, ''))) > ASC limit 1; > > Limit (cost=0.01..5.10 rows=1 width=68) (actual time=0.038..0.038 > rows=0 loops=1) > -> Index Scan using person_lowerfullname_idx on person > (cost=0.01..20.34 rows=4 width=68) (actual time=0.032..0.032 rows=0 > loops=1) > Index Cond: (((lower((COALESCE(firstname, ''::character > varying))::text) || lower((COALESCE(lastname, ''::character > varying))::text)) >= 'A'::text) AND ((lower((COALESCE(firstname, > ''::character varying))::text) || lower((COALESCE(lastname, > ''::character varying))::text)) < 'B'::text)) > Filter: ((lower((COALESCE(firstname, ''::character > varying))::text) || lower((COALESCE(lastname, ''::character > varying))::text)) ~~ 'A%'::text) > Total runtime: 0.138 ms > > > Works for me. I forgot to mention: I use UTF-8. IIRC, it works fine with C-locale without the "varchar_pattern_ops", which is why it works for you I think. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] PG won't use index on ORDER BY
On Thursday 09 August 2007 22:38:46 Andreas Joseph Krogh wrote: > On Thursday 09 August 2007 22:00:54 Gregory Stark wrote: > > "Andreas Joseph Krogh" <[EMAIL PROTECTED]> writes: > > > I create an index: > > > CREATE INDEX person_lowerfullname_idx ON > > > person((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, > > > ''))) varchar_pattern_ops); > > > > Why are you declaring it using the varchar_pattern_ops? > > > > The default operator set is the one you want for handling ordering. The > > pattern_ops operator set is for handling things like x LIKE 'foo%' > > Ooops, just fugured that out. But - it still doesn't use the index if I > remove the "varchar_pattern_ops". I solved it by adding a function: > > CREATE OR REPLACE FUNCTION concat_lower(varchar, varchar) RETURNS varchar > AS $$ > SELECT lower(coalesce($1, '')) || lower(coalesce($2, '')) > $$ LANGUAGE SQL IMMUTABLE; > > And than creating an index: > CREATE INDEX person_lowerfullname_idx ON person(concat_lower(firstname, > lastname)); > > Another question then: Why doesn't "varchar_pattern_ops" handle ordering? > This means I need 2 indexes on the columns I want to match with LIKE and > ORDER BY. Just doesn't seem right to need 2 "similar" indexes... Hmm, one more question: If I want to ORDER BY "created" too, the index is not used anymore: -- This uses index: EXPLAIN ANALYZE select firstname, lastname from person order by concat_lower(firstname, lastname) ASC limit 10; -- This doesn't EXPLAIN ANALYZE select firstname, lastname from person order by concat_lower(firstname, lastname) ASC, created DESC limit 10; I figured out that it's because they have different sort-order (ASC/DESC). If both are ASC, the index is used. Is there a way around this? -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] PG won't use index on ORDER BY
Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: >> Another question then: Why doesn't "varchar_pattern_ops" handle ordering? It does handle ordering, just not the ordering you're asking for here. If you substituted USING ~<~ for ASC you'd find that the pattern_ops index could be used for that. >> This means I need 2 indexes on the columns I want to match with LIKE and >> ORDER BY. Just doesn't seem right to need 2 "similar" indexes... If you want to use the same index for both, you have to run the database in C locale. Non-C locales generally define a sort ordering that is not compatible with LIKE searches. (The point of the pattern_ops opclass is really to force C-locale ordering of the index when the ordinary text comparison operators yield a different ordering.) > -- This doesn't > EXPLAIN ANALYZE select firstname, lastname from person order by > concat_lower(firstname, lastname) ASC, created DESC limit 10; This ORDER BY is asking for an ordering that is almost completely unrelated to the index's ordering. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] PG won't use index on ORDER BY
Andreas Joseph Krogh <[EMAIL PROTECTED]> writes:
> I forgot to mention: I use UTF-8. IIRC, it works fine with C-locale without
> the "varchar_pattern_ops", which is why it works for you I think.
That shouldn't make any difference, and doesn't for me in testing here:
regression=# select version();
version
---
PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20070626 (Red Hat 4.1.2-13)
(1 row)
regression=# show lc_collate;
lc_collate
en_US.utf8
(1 row)
regression=# show server_encoding ;
server_encoding
-
UTF8
(1 row)
regression=# \d person
Table "public.person"
Column | Type| Modifiers
---+---+-
id| integer | not null default
nextval('person_id_seq'::regclass)
firstname | character varying |
lastname | character varying |
Indexes:
"person_pkey" PRIMARY KEY, btree (id)
"person_lowerfullname_idx" btree ((lower(COALESCE(firstname, ''::character
varying)::text) || lower(COALESCE(lastname, ''::character varying)::text)))
regression=# explain select id from person order by (lower(COALESCE(firstname,
'')) || lower(COALESCE(lastname, ''))) ASC limit 1;
QUERY PLAN
-
Limit (cost=0.00..0.08 rows=1 width=68)
-> Index Scan using person_lowerfullname_idx on person (cost=0.00..62.25
rows=800 width=68)
(2 rows)
So there's something going on that you haven't told us about your installation.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] PG won't use index on ORDER BY
"Andreas Joseph Krogh" <[EMAIL PROTECTED]> writes: > Ooops, just fugured that out. But - it still doesn't use the index if I > remove > the "varchar_pattern_ops". I solved it by adding a function: Hm, well it does for me, you would have to post your explain analyze to see what's going on. > Another question then: Why doesn't "varchar_pattern_ops" handle ordering? > This > means I need 2 indexes on the columns I want to match with LIKE and ORDER BY. > Just doesn't seem right to need 2 "similar" indexes... If you initd in the C locale you only need one index. In other locales the collation order and the pattern order are different. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Indexing a field of type point
Hi, I realise this isn't strictly an SQL question, but I figured this list might be better suited than the general one. Please let me know if not. I have a table containing a field named location, of type point, i.e. a position in two dimensions. The table has several million records in, and I need to extract those records whose location value is contained within a certain bounding box. To do this efficiently (rather than as a linear scan), I would like to create an index over this field. However, using GIST under Postgresql 8.2.4 I can't do this: test=# create index points_location_index on points using gist (location); ERROR: data type point has no default operator class for access method "gist" HINT: You must specify an operator class for the index or define a default operator class for the data type. Looking through the available classes, there are none defined for points :-(. I have seen a post saying that one could use create index points_location_index on points using gist (location box_ops); but that comes back with the error that (rightly) box_ops doesn't have operators for data of type point. Is anyone aware of a way of creating a suitable index? I am aware of PostGIS, but would prefer not to have to rework a whole load of code to use the different geometrical field types it provides. Thanks for any help! David. -- David Cottingham Computer Laboratory, University of Cambridge http://www.cl.cam.ac.uk/users/dnc25/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Best Fit SQL query statement
All, 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 Regards, Kiran ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Race condition in resetting a sequence
Steve Midgley writes:
The code I provided to reset a primary key sequence is actually part of
Ruby on Rails core library - actually they use something very similar
to what I originally sent:
...
SELECT setval('#{sequence}', (SELECT COALESCE(MAX(#{pk})+(SELECT
increment_by FROM #{sequence}), (SELECT min_value FROM #{sequence}))
FROM #{table}), false)
Tom Lane wrote:
Ugh. That's completely unsafe/broken, unless they also use locking that
you didn't show.
...
It doesn't have a race condition "all by itself": it will do what it's
told. The problem with commands such as the above is that there's a
time window between calculating the max() and executing the setval(),
and that window is more than large enough to allow someone else to
insert a row that invalidates your max() computation. (Because of MVCC
snapshotting, the risk window is in fact as long as the entire
calculation of the max --- it's not just a few instructions as some
might naively think.)
Now it is possible to make this brute-force approach safe: you can lock
the table against all other modifications until you've applied your own
changes. But you pay a high price in loss of concurrency if you do
that.
All this trouble over semantically-significant ID columns seems to support the
camp that excoriates use of artificial ID columns and autoincrementation
altogether.
The usual argument in their favor is that they speed up performance, but this
epicyclic dance to accomodate FK references to autoincremented keys makes the
case that there is also a performance penalty, and in the more critical
performance area of code development and correctness than in the less critical
search speed area.
--
Lew
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Returnd Refcurser ( not fetchable
Sorry but this didn't help ...
Can not return next ... so I changed the returns as too but same effect as the
first function:
drop table foo;
create table foo(sirname text, name text);
insert into foo values ('Mueller', 'Marcus');
drop function getfoo(char(1));
create or replace function getfoo(char(1)) returns setof refcursor as
'
declare
ref refcursor;
begin
open ref for select * from foo where sirname like ''%'' || $1 || ''%'';
return next ref;
end;
'
LANGUAGE 'plpgsql' VOLATILE;
select * from getfoo('M');
-- fetch all in '';
ERROR: syntax error at or near "''" at character 15
--
cu
Chris
Pt! Schon vom neuen GMX MultiMessenger gehört?
Der kanns mit allen: http://www.gmx.net/de/go/multimessenger
---(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] Returnd Refcurser ( not fetchable
"Christian Kindler" <[EMAIL PROTECTED]> writes: > fetch all in ''; > ERROR: syntax error at or near "''" at character 15 I think you need double quotes not single quotes here. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Returnd Refcurser ( not fetchable
Ohhh Yes!
Thanks al lot. Sometimes you can't see the wood for the trees.
Thanks!
Chris
Original-Nachricht
Datum: Fri, 10 Aug 2007 00:31:03 -0600
Von: Michael Fuhr <[EMAIL PROTECTED]>
An: Christian Kindler <[EMAIL PROTECTED]>
CC: "Penchalaiah P." <[EMAIL PROTECTED]>, pgsql-sql@postgresql.org
Betreff: Re: [SQL] Returnd Refcurser ( not fetchable
> On Fri, Aug 10, 2007 at 08:16:28AM +0200, Christian Kindler wrote:
> > select * from getfoo('M');
> > -- fetch all in '';
> >
> > ERROR: syntax error at or near "''" at character 15
>
> The cursor name is an identifier so use double quotes instead of
> single quotes. Also, when using non-holdable cursors you'll need
> to be in a transaction block.
>
> begin;
> select * from getfoo('M');
> fetch all in "";
> commit;
>
> --
> Michael Fuhr
--
cu
Chris
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer
---(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] Returnd Refcurser ( not fetchable
create table foo(sirname text, name text); insert into foo values
('Mueller', 'Marcus');
create function getfoo(char(1)) returns refcursor as '
declare
ref refcursor;
begin
open ref for select * from foo where sirname like ''%'' || $1 ||
''%'';
return next ref;
return
end;
'
LANGUAGE 'plpgsql' VOLATILE;
Copy the above function and execute..i changed at return statement.
Thanks & Regards
Penchal Reddy
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Christian Kindler
Sent: Friday, August 10, 2007 11:28 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Returnd Refcurser ( not fetchable
Hi!
I have the Problem, that a function returns a refcursor and I am not
able to fetch them. I tried in several ways.
Can you please tell me how to get the cursor data.
Thanks
Chris
PS this is what I have:
create table foo(sirname text, name text);
insert into foo values ('Mueller', 'Marcus');
create function getfoo(char(1)) returns refcursor as
'
declare
ref refcursor;
begin
open ref for select * from foo where sirname like ''%'' || $1 ||
''%'';
return ref;
end;
'
LANGUAGE 'plpgsql' VOLATILE;
select * from getfoo('M');
* fetch all in '';
ERROR: syntax error at or near "''" at character 14
--
cu
Chris
GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS.
Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail
---(end of broadcast)---
TIP 6: explain analyze is your friend
Information transmitted by this e-mail is proprietary to Infinite Computer
Solutions and / or its Customers and is intended for use only by the individual
or the entity to which it is addressed, and may contain information that is
privileged, confidential or exempt from disclosure under applicable law. If you
are not the intended recipient or it appears that this mail has been forwarded
to you without proper authority, you are notified that any use or dissemination
of this information in any manner is strictly prohibited. In such cases, please
notify us immediately at [EMAIL PROTECTED] and delete this email from your
records.
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[SQL] pg_trgm installation
Hello, While installing pg_trgm for the similarity I am unable to install that properly and giving error messages. ./Makefile: line 2: PG_CPPFLAGS: command not found ./Makefile: line 4: MODULE_big: command not found ./Makefile: line 5: OBJS: command not found ./Makefile: line 7: DATA_built: command not found ./Makefile: line 8: DOCS: command not found ./Makefile: line 9: REGRESS: command not found ./Makefile: line 12: ifdef: command not found ./Makefile: line 13: shell: command not found ./Makefile: line 13: PGXS: command not found ./Makefile: line 14: PGXS: command not found ./Makefile: line 14: include: command not found ./Makefile: line 15: syntax error near unexpected token `else' ./Makefile: line 15: `else' I was trying to install PostgreSQL 8.1.2 but it is giving version "PostgreSQL 8.1.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 20060711 (Red Hat 4.1.1-8)". Could anyone please help me to get rid out of this pg_trgm issue? With Regards, Arindam Hore V2Solutions A New Vision to Solutions India: +91-22-66733201 ext --- 658 US : 1-408-454-6051 ext --- http://www.v2solutions.com TZ : +5:30 GMT
[SQL] Using function like where clause
Hello, I have 2 questions. 1) Can I use a function that will return a string in a where clause like bellow? select * from table where my_function_making_where() and another_field = 'another_think' 2) Can I use a function that will return a string to return the list of columns that I want to show like below? select my_function_making_list_of_columns() from table where field_test = 'mydatum' Thanks ---(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] Returnd Refcurser ( not fetchable
Hi!
I have the Problem, that a function returns a refcursor and I am not able to
fetch them. I tried in several ways.
Can you please tell me how to get the cursor data.
Thanks
Chris
PS this is what I have:
create table foo(sirname text, name text);
insert into foo values ('Mueller', 'Marcus');
create function getfoo(char(1)) returns refcursor as
'
declare
ref refcursor;
begin
open ref for select * from foo where sirname like ''%'' || $1 || ''%'';
return ref;
end;
'
LANGUAGE 'plpgsql' VOLATILE;
select * from getfoo('M');
* fetch all in '';
ERROR: syntax error at or near "''" at character 14
--
cu
Chris
GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS.
Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Returnd Refcurser ( not fetchable
On Fri, Aug 10, 2007 at 08:16:28AM +0200, Christian Kindler wrote:
> select * from getfoo('M');
> -- fetch all in '';
>
> ERROR: syntax error at or near "''" at character 15
The cursor name is an identifier so use double quotes instead of
single quotes. Also, when using non-holdable cursors you'll need
to be in a transaction block.
begin;
select * from getfoo('M');
fetch all in "";
commit;
--
Michael Fuhr
---(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
