PostgreSQL - SQL

[SQL] Boolean without default declared

By Jon Collette at Aug 15, 2007, 7:36 pm UTC

If a column with a boolean datatype doesn't have a default value. What type of value is set if nothing is inserted into that column? Here is my test table and the queries I have tried. I can't seem to be able to select the rows where happy has no value. Table "public.users" Column | Type |... More...

If a column with a boolean datatype doesn't have a default value.  What 
type of value is set if nothing is inserted into that column?  Here is 
my test table and the queries I have tried.  I can't seem to be able to 
select the rows where happy has no value.

            Table "public.users"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | character varying(32) |
 email  | text                  |
 happy  | boolean               |
 money  | numeric               |


*select * from users;                *
 id | email  | happy |  money 
----+--------+-------+---------
 4  | me     |       | 1324.23
 4  | me     |       | 1324.23
 3  | as     |       |   123.2
 1  | afjssd | t     |       

*select * from users where happy;*
 id | email  | happy | money
----+--------+-------+-------
 1  | afjssd | t     |     

*select * from users where not happy;*
id | email | happy | money
----+-------+-------+-------
(0 rows)

*select * from users where happy = NULL;*
id | email | happy | money
----+-------+-------+-------
(0 rows)

*select * from users where happy = '';*
ERROR:  invalid input syntax for type boolean: ""

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

2 Replies

[SQL] When is a shared library unloaded?

By Jon Horsman at Aug 15, 2007, 1:16 pm UTC

I have a shared library loaded into postgresql using CREATE FUNCTION medbevent() RETURNS trigger as '/usr/lib/pgsql/libmedbevent.so' language 'C'; I'm randomly having issues with my triggers not firing (it turns out they are getting removed) and when i turned INFO logs on i saw the following... More...

I have a shared library loaded into postgresql using

CREATE FUNCTION medbevent() RETURNS trigger as
'/usr/lib/pgsql/libmedbevent.so' language 'C';

I'm randomly having issues with my triggers not firing (it turns out
they are getting removed) and when i turned INFO logs on i saw the
following

2007-08-14 13:41:44.740305500 LOCATION:  _fini, medbevent_init.c:129
2007-08-14 13:41:45.790850500 INFO:  00000: medbeventlib -Unloading
library _fini() is called

Could someone please explain when a shared library gets
loaded/unloaded and how this all works?  I'm getting the above logs
when i wouldn't expect to see the library be unloaded which is causing
me problems.  Could the library get unloaded in this manner due to an
internal error in the library?

Thanks,

Jon.

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

2 Replies

Re: [NOVICE] Install two different versions of postgres which should run in parallel

By Oliver Elphick at Aug 15, 2007, 12:42 pm UTC

If you run Debian or Ubuntu you can do this with the package system. apt-get install postgresql-8.2 postgresql-7.4 The two versions are automatically installed to run on different ports. Look at the docs and manpages for the postgresql-common package to see how it works. If you can't do that, you... More...

On Fri, 2007-08-10 at 14:33 +0300, Loredana Curugiu wrote:
> Hi all,
>
> I need to have two different vesions of postgres running in parallel
> on
> different ports. Does anyone knows how to install two different
> versions
> of postgres (7.4.5 and 8.2.4) on the same computer? I am using Linux
> operating system.

If you run Debian or Ubuntu you can do this with the package system.

  apt-get install postgresql-8.2 postgresql-7.4

The two versions are automatically installed to run on different ports.
Look at the docs and manpages for the postgresql-common package to see
how it works.

If you can't do that, you can install a different version from source in
a different directory tree; then have its postmaster run on a different
port.  Specify the port number when connecting so as to get to the
postmaster you want.

--
Oliver Elphick [email protected: o...@lfix.co.uk]
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [email protected: majo...@postgresql.org] so that your
message can get through to the mailing list cleanly

2 Replies

[JDBC] Re: [NOVICE] Install two different versions of postgres which should run in parallel

By Sean Davis at Aug 15, 2007, 11:58 am UTC

You can install from source and provide the --prefix argument to the configure command. Sean ---------------------------(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... More...

Loredana Curugiu wrote:
> Hi all,
>
> I need to have two different vesions of postgres running in parallel on
> different ports. Does anyone knows how to install two different versions
> of postgres (7.4.5 and 8.2.4) on the same computer? I am using Linux
> operating system.

You can install from source and provide the --prefix argument to the
configure command.

Sean

---------------------------(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

4 Replies

[SQL] populate value of column

By novice at Aug 15, 2007, 03:15 am UTC

I have a table CREATE TABLE meter meter_id integer NOT NULL, area_no integer NOT NULL, CONSTRAINT meter_pkey PRIMARY KEY (meter_id) INSERT INTO meter(meter_id, no_of_bays) VALUES (1001, 4); INSERT INTO meter(meter_id, no_of_bays) VALUES (1012, 6); select meter_id, area_no from meter; meter_id |... More...

I have a table

CREATE TABLE meter
(
  meter_id integer NOT NULL,
  area_no integer NOT NULL,
  CONSTRAINT meter_pkey PRIMARY KEY (meter_id)
) ;

INSERT INTO meter(meter_id, no_of_bays) VALUES (1001, 4);
INSERT INTO meter(meter_id, no_of_bays) VALUES (1012, 6);

select meter_id, area_no from meter;

meter_id | no_of_bays
----------+------------
     1001 |         4
     1012 |         6


How can I generate the following result?

meter_id | bay
----------+------------
     1001 |         01
     1001 |         02
     1001 |         03
     1001 |         04
     1012 |         01
     1012 |         02
     1012 |         03
     1012 |         04
     1012 |         05
     1012 |         06

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

4 Replies

[SQL] Best Fit SQL query statement

By Kiran at Aug 14, 2007, 1:59 pm UTC

All, Could anyone help me in writing Best Fit SQL statement. Suppose we have table t1 with coloumn t1 (text) with following rows. 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... More...

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

7 Replies

[SQL] Authentification failed

By Judith Altamirano Figueroa at Aug 14, 2007, 11:40 am UTC

Hello everybody!! I'm trying in SUSE to connect to a postgres db and this is the error: Ident Authentification failed for user <<root>> I'm already created the user with createuser root, but the error persist, I would aprecciate some help, thanks in advanced ---------------------------(end of... More...

Hello everybody!!

I'm trying in SUSE to connect to a postgres db and this is the error:

     Ident Authentification failed for user <<root>>

I'm already created the user with createuser root, but the error
persist, I would aprecciate some help, thanks in advanced

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

5 Replies

Re: [SQL] Install two different versions of postgres which should run in parallel

By gh...@fmed.uba.ar at Aug 14, 2007, 07:37 am UTC

Shure. You just have to take care about --prefix (executable path) and --with-pgport (default port) when you ./configure, and also take care of the datadir you chose when executing initdb. Good luck. Gerardo ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill... More...

Loredana Curugiu wrote:

>Hi all,
>
>I need to have two different vesions of postgres running in parallel on
>different ports. Does anyone knows how to install two different versions
>of postgres (7.4.5 and 8.2.4) on the same computer? I am using Linux
>operating system.
>
>Any information would greatly be appreciated.
>
>Loredana
>
>  
>

Shure. You just have to take care about --prefix (executable path) and
--with-pgport (default port) when you ./configure, and also take care of
the datadir you chose when executing initdb.

Good luck.
Gerardo

3 Replies

[SQL] how to moce back in refcursor

By Christian Kindler at Aug 14, 2007, 06:17 am UTC

Hi I have never worked with cursors but for now I have to step back on special events to calculate a difference. So I have this sample code tested but I can not "move" in my cursor. I have lokke at docs but this didn't help me. Can you say how to declare my cursor to step back? Thank You Chris PS... More...

Hi

I have never worked with cursors but for now I have to step back on special events to calculate a difference. So I have this sample code tested but I can not "move" in my cursor. I have lokke at docs but this didn't help me. Can you say how to declare my cursor to step back?

Thank You
Chris

PS code sample
create table fimi.eodbar(fi_id int8, market_id int8);
insert into fimi.eodbar values(322,200);

create or replace function fimi.test("fiid" int8) returns setof int as
$BODY$
DECLARE
  cur1 REFCURSOR;
  foo int;

BEGIN
OPEN cur1 FOR execute('select market_id from fimi.eodbar where fi_id=' || "fiid");

   loop
    fetch cur1 into foo;

    if not found then
       exit ;
    else
       -- move -1 in cur1;
       fetch cur1 into foo;
       return next foo;
    end if;
   
   end loop;
close cur1;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION fimi.getfoo(bpchar) OWNER TO fimi;

select * from fimi.test(322);

--
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 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

2 Replies

[SQL] Getting pk of the most recent row, in a group by

By Bryce Nesbitt at Aug 13, 2007, 11:19 pm UTC

I've got a table of "coupons" which have an expiration date. For each type of coupon, I'd like to get the primary key of the coupon which will expire first. # create table coupon coupon_id serial primary key, type varchar(255), expires date insert into coupon values(DEFAULT,'free','2007-01-01');... More...

I've got a table of "coupons" which have an expiration date.  For each
type of coupon, I'd like to get the primary key of the coupon which will
expire first.

# create table coupon
(
        coupon_id serial primary key,
        type varchar(255),
        expires date
);
insert into coupon values(DEFAULT,'free','2007-01-01');
insert into coupon values(DEFAULT,'free','2007-01-01');
insert into coupon values(DEFAULT,'free','2007-06-01');
insert into coupon values(DEFAULT,'free','2007-06-01');
insert into coupon values(DEFAULT,'50%','2008-06-01');
insert into coupon values(DEFAULT,'50%','2008-06-02');
insert into coupon values(DEFAULT,'50%','2008-06-03');

The desired query would look like:

# select coupon_id,type,expires from coupon where type='free' order by
expires limit 1;
 coupon_id | type |  expires  
-----------+------+------------
         1 | free | 2007-01-01


But be grouped by type:

# select type,min(expires),count(*) from coupon group by type;
 type |    min     | count
------+------------+-------
 free | 2007-01-01 |     4    ; pk=1
 50%  | 2008-06-01 |     3    ; pk=5

In the second example, is it possible to get the primary key of the row
with the minimum expires time?

--
----
Visit http://www.obviously.com/


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

3 Replies

[SQL] Comparing two slices within one table efficiently

By Ken Simpson at Aug 13, 2007, 7:42 pm UTC

I have a table with the following simplified form: create table t ( run_id integer, domain_id integer, mta_id integer, attribute1 integer, attribute2 integer, unique(run_id, domain_id, mta_id) The table has about 1 million rows with run_id=1, another 1 million rows with run_id=2, and so on. I need... More...

I have a table with the following simplified form:

create table t (
run_id integer,
domain_id integer,
mta_id integer,
attribute1 integer,
attribute2 integer,
unique(run_id, domain_id, mta_id)
);

The table has about 1 million rows with run_id=1, another 1 million rows with run_id=2, and so on.

I need to efficiently query the differences between "runs" - i.e. For each (domain_id, mta_id) tuple in run 1, is there a coresponding tuple in run 2 where either attribute1 or attribute2 have changed?

The only way I have been able to think of doing this so far is an o(n^2) search, which even with indexes takes a long time. e.g.

select * from t t1 where exists (select 1 from t t2 where t2.mta_id=t1.mta_id and t2.domain_id=t1.domain_id and (t2.attribute1 != t1.attribute1 or t2.attribute2 != t1.attribute2)

This query takes millenia...

Any help would be greatly appreciated. I hope I am naively missing some obvious alternative strategy, since this sort of operation must be common in databases.

Thanks,
Ken

--
Ken Simpson, CEO
MailChannels Corporation
Reliable Email Delivery (tm)
http://www.mailchannels.com
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

5 Replies

[SQL] Index usage in order by with multiple columns in order-by-clause

By Andreas Joseph Krogh at Aug 11, 2007, 8:14 pm UTC

I have the following test-case: CREATE TABLE test( name varchar PRIMARY KEY, value varchar NOT NULL, created timestamp not null create index test_lowernamevalue_idx ON test ((lower(name) || lower(value))= create index test_lowernamevaluecreated_idx ON test ((lower(name) ||=20 lower(value)),... More...

I have the following test-case:

CREATE TABLE test(
name varchar PRIMARY KEY,
value varchar NOT NULL,
created timestamp not null
);

create index test_lowernamevalue_idx ON test ((lower(name) || lower(value)));
create index test_lowernamevaluecreated_idx ON test ((lower(name) ||
lower(value)), created);

andreak=# EXPLAIN ANALYZE select * from test order by lower(name) ||
lower(value) ASC, created ASC;
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_lowernamevaluecreated_idx on test  (cost=0.00..61.58 
rows=770 width=72) (actual time=0.013..0.013 rows=0 loops=1)
Total runtime: 0.127 ms
(2 rows)

andreak=# EXPLAIN ANALYZE select * from test order by lower(name) ||
lower(value) ASC, created DESC;
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Sort  (cost=60.39..62.32 rows=770 width=72) (actual time=0.034..0.034 rows=0 
loops=1)
   Sort Key: (lower((name)::text) || lower((value)::text)), created
   ->  Seq Scan on test  (cost=0.00..23.47 rows=770 width=72) (actual 
time=0.004..0.004 rows=0 loops=1)
Total runtime: 0.123 ms
(4 rows)

As the EXPLAIN-output shows, the index is not used when sort-ordering differs
in the two order-by-columns.
Is there a way I can have multiple columns in the ORDER BY clause, each with
different ASC/DESC-order and still use an index to speed up sorting?

In my application I often have a need to sort by more than 3 columns, so I'm
really wondering if there is a way to make sorting of multiple columsn (each
which may have different sort-order) use an index? Preferrably without having
to create 2^N indexes.

--
Andreas Joseph Krogh <andreak@officenet.no>
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: majo...@postgresql.org] so that your
message can get through to the mailing list cleanly

6 Replies

[SQL] Trigger Procedure Error: NEW used in query that is not in a rule

By Javier Fonseca V. at Aug 11, 2007, 7:07 pm UTC

Hello. I'm doing a Trigger Procedure in pl/pgSQL. It makes some kind of auditing. I think that it's working alright except for the next line: EXECUTE 'INSERT INTO ' || quote_ident(somedynamictablename) || ' SELECT new.*'; PostgreSQL keeps telling me: "ERROR: NEW used in query that is not in a... More...

Hello.

I'm doing a Trigger Procedure in pl/pgSQL.  It makes some kind of auditing.

I think that it's working alright except for the next line:

EXECUTE 'INSERT INTO ' || quote_ident(somedynamictablename) || ' SELECT
new.*';

PostgreSQL keeps telling me: "ERROR: NEW used in query that is not in a
rule".  I think that this NEW problem is because of the scope of the EXECUTE
statement (outside the scope of the trigger), so it doesn't recognize the
NEW record.

Maybe I could fix it concatenating column names and the 'new' values but I
want to do my trigger as flexible as possible (I have several tables to
audit).

Somebody has any suggestion?

Thanks a lot,

Javier

5 Replies

[SQL] foreign key pointing to diff schema?

By gh...@fmed.uba.ar at Aug 10, 2007, 10:37 pm UTC

Hi all. Can i make a FK who points a table in a different schema? Or this is implemented via a trigger by my own? Thanks! Gerardo ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to... More...

Hi all. Can i make a FK who points a table in a different schema? Or this
is implemented via a trigger by my own?

Thanks!
Gerardo

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [email protected: majo...@postgresql.org] so that your
message can get through to the mailing list cleanly

2 Replies

[SQL] Increment a sequence by more than one

By stevemidgley at Aug 10, 2007, 9:00 pm UTC

Hi, I'm writing an import app in a third party language. It's going to use "copy to" to move data from STDIN to a postgres (8.2) table. There are some complexities though: it's going to copy the records to a "versioned" table first, and then at a later time the records will be copied by a different... More...

Hi,

I'm writing an import app in a third party language. It's going to use
"copy to" to move data from STDIN to a postgres (8.2) table. There are
some complexities though: it's going to copy the records to a
"versioned" table first, and then at a later time the records will be
copied by a different procedure to the "live" table. The live table and
versioned table are identical in terms of their field definitions. But
there is no sequence associated with the versioned table (whose primary
key is "id" plus "import_group_id", whereas the live table's pk is just
"id"). So all versioned table entries must already "know" what their id
would be in the live table. (This makes sense for other business
process we have, but it's a bit of a problem in this instance).

My problem: I'd like to be able to grab a block of id's from the live
table's pk sequence. So let's say my importer has 5,000 new rows to
import and the current max pk in the live table is 540,203. I'd like to
be able to increment the primary key sequence in such a way that I get
a block of ids all to myself and the sequence is reset to 545,203 with
a guarantee that all the id's between 540203 and 545203 are unused.

I'm guessing this can be done with a stored procedure, but if possible
I'd like to be able to run this command from my third party app without
calling a stored procedure (we try not to use stored procedures here
b/c we code entirely in this third party language - if we had to, it's
possible we could install a stored procedure though).

But since I've seen so much magic on display from people on this list,
I'm going to ask if it's possible to do this solely from PG SQL sent
from a third party language? The main tricky bit seems to be ensuring
that everything is locked so two such increment calls at the same time
don't yield overlapping blocks of ids. Is there a way to "lock" the
sequence generator for the duration of a "nextval" and "setval" call?
Since pk sequence functions like nextval cannot be rolled back, I'm
guessing that "begin/end" won't do the job?

I.e:

-- need "magic lock" statement on pk sequence here
nextval
-- returns 540203
setval(545203)
-- now sequence is set to where I want it and I "own" 5000 id's
-- release magic lock here

My fallback is to just have a routine that calls "nextval" a bunch of
times and stores all the id's it gets - they may or may not be
sequential but they'll be unique. This is going to be a really slow way
to get a large number of id's of course and just seems plain
wrongheaded in many ways.

Any insights? All help is appreciated and input on a better way to
solve the problem completely is of course welcome as well.

Sincerely,

Steve

14 Replies

[SQL] Join optimization

By Pablo Barrón at Aug 10, 2007, 09:51 am UTC

Hi! I've been trying to optimize a query in which I join several tables, since I've seen it takes about 2 seconds, which is way too much. Well, the query is the following, I'm using LEFT OUTER JOIN just when the tables can have NULL results, plain JOIN otherwise: select ="select... More...

Hi!

I've been trying to optimize a query in which I join several tables, since
I've seen it takes about 2 seconds, which is way too much.

Well, the query is the following, I'm using LEFT OUTER JOIN just when the
tables can have NULL results, plain JOIN otherwise:

select ="select to_char(a.fecha_publicacion,'dd/MM/yyyy')," +
                "c.nombre,date_part('year',CURRENT_TIMESTAMP)-b.ano as
edad," +
                "b.alzada,d.nombre,e.nombre,a.precio_a_mostrar,f.nombre," +
                "a.destacado,a.visto,h.nombre,m.nombre,g.idprovincia," +
                "g.provincia,b.raza_id,b.raza,b.disciplina_id" +
                ",b.disciplina " +
                " from anuncio a JOIN caballo b " +
                "ON (a.producto_id=b.id) " +
                "JOIN raza c ON (b.raza_id=c.id) " +
                " LEFT OUTER JOIN disciplina d " +
                "ON (b.disciplina_id=d.id) " +
                "LEFT OUTER JOIN nivel_disciplina e " +
                "ON (b.disciplina_id=e.disciplina_id " +
                "and b.nivel_id=e.nivel) " +
                " JOIN anunciante_datos g ON (a.id_anunciante_datos = g.id)"
+
                "JOIN provincia f ON (g.idprovincia=f.id) " +
                "JOIN categoria h ON (a.categoria_id=h.id) " +
                " LEFT OUTER JOIN sexo_caballo m ON " +
                "(b.sexo_id=m.id) "+
                "WHERE a.id=?";

I'd thought I could pull a trick on the fact that even though this "anuncio"
table (a) is relatively big (a few thousand entries), I really just need to
retrieve one result from it and combine it with the other tables. This
result would be that which matches with the ? in the a.id condition, which
is the Primary Key of this "anuncio" table. The other data is derived from
this specific result in "anuncio" (which btw means "advertisment" in
spanish). For example, "caballo" means "horse" in spanish, and its data is
retrieved as a horse related to the advertisement that sells such a horse.

My idea was to try to cut as soon as possible the few thousands of registers
from the "anuncio" table so that it might make it less costly to make the
query. For instance I tried this, but with no visible results:

                " from anuncio a JOIN caballo b " +
                "ON (a.id=? AND a.producto_id=b.id) " +

Any ideas on how to critically optimize the query?

Thank you lots =)

0 Replies

[SQL] Using function like where clause

By Ranieri Mazili at Aug 10, 2007, 08:00 am UTC

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... More...

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 5: don't forget to increase your free space map settings

2 Replies

[SQL] Returnd Refcurser (<unnamed portal x> not fetchable

By Christian Kindler at Aug 10, 2007, 06:35 am UTC

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');... More...

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 '<unnamed portal 11>';

ERROR:  syntax error at or near "'<unnamed portal 11>'" 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

5 Replies

[SQL] PG won't use index on ORDER BY <expression>

By Andreas Joseph Krogh at Aug 10, 2007, 03:10 am UTC

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=20 person((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname,... More...

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 <andreak@officenet.no>
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

8 Replies

[SQL] Indexing a field of type point

By David Cottingham at Aug 9, 2007, 5:33 pm UTC

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... More...

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

2 Replies