date:20110112

Re: [SQL] help needs in converting db2 function in postgresql.

2011-01-12 Thread Amar Dhole

Thanks this solves my problem..

 



From: filip.rembialkow...@gmail.com [mailto:filip.rembialkow...@gmail.com] On 
Behalf Of Filip Rembialkowski
Sent: Wednesday, January 12, 2011 1:41 AM
To: Amar Dhole
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] help needs in converting db2 function in postgresql.

 

 

2011/1/11 Amar Dhole 

Hi,
I need helping converting following db2 function in postgresql function.
Any pointer will be great help in proceeding me ahead.

CREATE FUNCTION in_liststring ( string CLOB(64K) )
  RETURNS TABLE ( ordinal INTEGER, index INTEGER )
  LANGUAGE SQL
  DETERMINISTIC
  NO EXTERNAL ACTION
  CONTAINS SQL
  RETURN
 WITH t(ordinal, index) AS
( VALUES ( 0, 0 )
  UNION ALL
  SELECT ordinal+1, COALESCE(NULLIF(
-- find the next delimiter ','
LOCATE(',', string, index+1), 0),
LENGTH(string)+1)
  FROM   t
 -- to prevent a warning condition for infinite
 -- recursions, we add the explicit upper
 -- boundary for the "ordinal" values
  WHERE  ordinal < 1 AND
 -- terminate if there are no further delimiters
 -- remaining
 LOCATE(',', string, index+1) <> 0 )
 SELECT ordinal, index
 FROM   t
 UNION ALL
 -- add indicator for the end of the string
 SELECT MAX(ordinal)+1, LENGTH(string)+1
 FROM   t
;

commit;

DROP FUNCTION INSTRTBL;

CREATE FUNCTION INSTRTBL ( string CLOB(64K) )
  RETURNS TABLE ( INSTRTBL CLOB(64K) )
  LANGUAGE SQL
  DETERMINISTIC
  NO EXTERNAL ACTION
  CONTAINS SQL
  RETURN
 WITH t(ordinal, index) AS
( SELECT ordinal, index
  FROM   TABLE ( in_liststring(string) ) AS x )
 SELECT SUBSTR(string, t1.index+1, t2.index - t1.index - 1)
 -- the join below makes sure that we have the lower and
 -- upper index where we can find each of the ',' delimiters
 -- that are separating the INSTRTBL.  (For this, we exploit
 -- the additional indexes pointing to the beginning and end
 -- of the string.)
 FROM   t AS t1 JOIN t AS t2 ON
   ( t2.ordinal = t1.ordinal+1 )
;




create or replace function instrtbl(text)
returns table(instrtbl text)
language sql
immutable
strict
as $$
SELECT * FROM regexp_split_to_table($1, ',')
$$;

fi...@filip=# select * from instrtbl( 'one, two, really long three' );
  instrtbl  

 one
  two
  really long three
(3 rows)

I love PostgreSQL.

Filip

[SQL] Implement PostgreSQL full text search

2011-01-12 Thread -

Hello everyone.
I'm trying to create search with pagination and filters on a website. I
stumbled with the search for the words. It could do with ILIKE but the table
will have many records, was looking at PostgreSQL full text search, but do
not quite understand how to implement. Someone could help me with
implementation?

my select is

SELECT entries. "id" entries. "title" entries. "description" entries.
"price"
FROM
   (SELECT ROW_NUMBER () OVER (
ORDER BY items. "date" DESC
   ) as Row,
   items. "id" items. "title" items. "description" items. "price "
   FROM items, provinces
   WHERE
   items. "prov_id" = provinces. "prov_id"
AND items. "prov_id"= 2

- I try to change here for PostgreSQL full text search
AND (items. "description" ILIKE ('% TEST search Postgre%') OR
items. "title" ILIKE ('% search Postgre% TEST'))
   ) as entries

I want to look for words entered in the title and description columns of the
table.
Thank you very much for any help.



En Español

Hola a todos.
Estoy intentando crear busqueda con paginacion y filtros en una pagina web.
Me tropeze con la busqueda por las palabras. Se podria hacer con ILIKE pero
la tabla va a tener muchos registros, estaba mirando a postgreSQL full text
search, pero no entiendo bien como implementar. Me podria ayudar alquien con
implementacion?

mi select es

SELECT entries."id", entries."title", entries."description", entries."price"
FROM
  (SELECT ROW_NUMBER() OVER (
  ORDER BY items."date" DESC
  ) as Row,
  items."id", items."title", items."description", items."price"
  FROM items, provinces
  WHERE
  items."prov_id"=provinces."prov_id"
AND items."prov_id"=2

-- I try to change here for postgreSQL full text search
AND (items."description" ILIKE('%TEST search postgre%') OR
items."title" ILIKE('%TEST search postgre%'))
  ) as entries

Quiero que busque palabras introducidas en las columnas title y description
de la tabla.
Muchas gracias por cualquier ayuda.

[SQL] INSERT/UPDATE .. RETURNING

2011-01-12 Thread Protasov Vladimir

Hello everybody, 
I have the table (named for example "mytable") and trigger on insert. The 
trigger do the following (in real trigger the logic is more complicated, but 
there is no difference for this question):
INSERT INTO another_table VALUES (NEW.*);
RETURN NULL;

And i want to do the following:
INSERT INTO "mytable" VALUES ('blah', 'blah', 'blah') RETURNING *;

But result is empty. I've tried to add rule on insert into "mytable" with 
returning clause, but there is the problem with one: returning clause will not 
be invoked if insert returns nothing. But i really need return value for the 
insert.
Any ideas?

-- 
Regards,
Vladimir Protasov.

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

[SQL] joining one record according to max value

2011-01-12 Thread George Francis

The following sql is intended to produce  a list of all unique SOURCEs,
along with the corresponding LEVEL.NAME for LEVEL with highest SCORE value
via SOURCELEVEL.

I'd like to know why it doesn't return a SCORE value for SOURCE with id 3,
despite there being a SOURCELEVEL entry for it.

Many thanks,


drop table if exists source;

drop table if exists sourcelevel;

drop table if exists level;


create table source ( source_id int );

create table sourcelevel ( source_id int, level_id int);

create table level ( level_id int, score int, name text );


insert into source values (1), (2), (3);

insert into level values ( 1, 10 ,'alpha' ), ( 2, 20, 'beta' ), ( 3, 15,
'kappa' );

insert into sourcelevel  values ( 1, 1 ), ( 1,2 ), ( 3,3 );


select source.source_id, score, name from source

left join ( select * from sourcelevel, level where sourcelevel.level_id =
level.level_id order by score desc limit 1 )

as temp on temp.source_id = source.source_id;

-- 
George

Re: [SQL] joining one record according to max value

2011-01-12 Thread George Francis

hmm, but if I try to constrain the inner query to the source_id of the outer
query I get an error as follows:

select source.source_id, score, name from source
left join ( select * from sourcelevel, level where sourcelevel.level_id =
level.level_id and sourcelevel.source_id = source.source_id order by score
desc limit 1 )
as temp on temp.source_id = source.source_id;

   ERROR:  invalid reference to FROM-clause entry for table "source"

LINE 14: ...l_id = level.level_id and sourcelevel.source_id = source.sou...

  ^

HINT:  There is an entry for table "source", but it cannot be referenced
from this part of the query.



** Error **


ERROR: invalid reference to FROM-clause entry for table "source"

SQL state: 42P01

Hint: There is an entry for table "source", but it cannot be referenced from
this part of the query.

Character: 601


On Wed, Jan 12, 2011 at 5:35 PM, Jasmin Dizdarevic <
jasmin.dizdare...@gmail.com> wrote:

> Because of the "score desc limit 1". The subselect returns only the higest
> score and this is level 2.
>
> 2011/1/12 George Francis 
>
>  The following sql is intended to produce  a list of all unique SOURCEs,
>> along with the corresponding LEVEL.NAME for LEVEL with highest SCORE
>> value via SOURCELEVEL.
>>
>> I'd like to know why it doesn't return a SCORE value for SOURCE with id 3,
>> despite there being a SOURCELEVEL entry for it.
>>
>> Many thanks,
>>
>>
>> drop table if exists source;
>>
>> drop table if exists sourcelevel;
>>
>> drop table if exists level;
>>
>>
>> create table source ( source_id int );
>>
>> create table sourcelevel ( source_id int, level_id int);
>>
>> create table level ( level_id int, score int, name text );
>>
>>
>> insert into source values (1), (2), (3);
>>
>> insert into level values ( 1, 10 ,'alpha' ), ( 2, 20, 'beta' ), ( 3, 15,
>> 'kappa' );
>>
>> insert into sourcelevel  values ( 1, 1 ), ( 1,2 ), ( 3,3 );
>>
>>
>> select source.source_id, score, name from source
>>
>> left join ( select * from sourcelevel, level where sourcelevel.level_id =
>> level.level_id order by score desc limit 1 )
>>
>> as temp on temp.source_id = source.source_id;
>>
>> --
>> George
>>
>
>


-- 
George Francis
e-mail: gfranc...@gmail.com

Re: [SQL] joining one record according to max value

2011-01-12 Thread Jasmin Dizdarevic

It's currently late, so excuse me if I'm wrong. Is this, what you like to
have?

select sl.source_id, max(score)  from source s
inner join sourcelevel sl on s.source_id = sl.source_id
inner join level l on sl.level_id = l.level_id
group by 1

This is the highest score per source_id.

2011/1/12 George Francis 

> hmm, but if I try to constrain the inner query to the source_id of the
> outer query I get an error as follows:
>
> select source.source_id, score, name from source
> left join ( select * from sourcelevel, level where sourcelevel.level_id =
> level.level_id and sourcelevel.source_id = source.source_id order by score
> desc limit 1 )
>  as temp on temp.source_id = source.source_id;
>
> ERROR:  invalid reference to FROM-clause entry for table "source"
>
> LINE 14: ...l_id = level.level_id and sourcelevel.source_id = source.sou...
>
>   ^
>
> HINT:  There is an entry for table "source", but it cannot be referenced
> from this part of the query.
>
>
>
> ** Error **
>
>
> ERROR: invalid reference to FROM-clause entry for table "source"
>
> SQL state: 42P01
>
> Hint: There is an entry for table "source", but it cannot be referenced
> from this part of the query.
>
> Character: 601
>
>
> On Wed, Jan 12, 2011 at 5:35 PM, Jasmin Dizdarevic <
> jasmin.dizdare...@gmail.com> wrote:
>
>> Because of the "score desc limit 1". The subselect returns only the higest
>> score and this is level 2.
>>
>> 2011/1/12 George Francis 
>>
>>  The following sql is intended to produce  a list of all unique SOURCEs,
>>> along with the corresponding LEVEL.NAME for LEVEL with highest SCORE
>>> value via SOURCELEVEL.
>>>
>>> I'd like to know why it doesn't return a SCORE value for SOURCE with id
>>> 3, despite there being a SOURCELEVEL entry for it.
>>>
>>> Many thanks,
>>>
>>>
>>> drop table if exists source;
>>>
>>> drop table if exists sourcelevel;
>>>
>>> drop table if exists level;
>>>
>>>
>>> create table source ( source_id int );
>>>
>>> create table sourcelevel ( source_id int, level_id int);
>>>
>>> create table level ( level_id int, score int, name text );
>>>
>>>
>>> insert into source values (1), (2), (3);
>>>
>>> insert into level values ( 1, 10 ,'alpha' ), ( 2, 20, 'beta' ), ( 3, 15,
>>> 'kappa' );
>>>
>>> insert into sourcelevel  values ( 1, 1 ), ( 1,2 ), ( 3,3 );
>>>
>>>
>>> select source.source_id, score, name from source
>>>
>>> left join ( select * from sourcelevel, level where sourcelevel.level_id =
>>> level.level_id order by score desc limit 1 )
>>>
>>> as temp on temp.source_id = source.source_id;
>>>
>>> --
>>> George
>>>
>>
>>
>
>
> --
> George Francis
> e-mail: gfranc...@gmail.com
>

Re: [SQL] joining one record according to max value

2011-01-12 Thread George Francis

Close!  but I need the name of the LEVEL with highest score, and I dont
think I can get it this way.
Thanks for trying though!

On Wed, Jan 12, 2011 at 5:49 PM, Jasmin Dizdarevic <
jasmin.dizdare...@gmail.com> wrote:

> It's currently late, so excuse me if I'm wrong. Is this, what you like to
> have?
>
> select sl.source_id, max(score)  from source s
> inner join sourcelevel sl on s.source_id = sl.source_id
> inner join level l on sl.level_id = l.level_id
> group by 1
>
> This is the highest score per source_id.
>
> 2011/1/12 George Francis 
>
>> hmm, but if I try to constrain the inner query to the source_id of the
>> outer query I get an error as follows:
>>
>> select source.source_id, score, name from source
>> left join ( select * from sourcelevel, level where sourcelevel.level_id =
>> level.level_id and sourcelevel.source_id = source.source_id order by score
>> desc limit 1 )
>>  as temp on temp.source_id = source.source_id;
>>
>> ERROR:  invalid reference to FROM-clause entry for table "source"
>>
>> LINE 14: ...l_id = level.level_id and sourcelevel.source_id =
>> source.sou...
>>
>>   ^
>>
>> HINT:  There is an entry for table "source", but it cannot be referenced
>> from this part of the query.
>>
>>
>>
>> ** Error **
>>
>>
>> ERROR: invalid reference to FROM-clause entry for table "source"
>>
>> SQL state: 42P01
>>
>> Hint: There is an entry for table "source", but it cannot be referenced
>> from this part of the query.
>>
>> Character: 601
>>
>>
>> On Wed, Jan 12, 2011 at 5:35 PM, Jasmin Dizdarevic <
>> jasmin.dizdare...@gmail.com> wrote:
>>
>>> Because of the "score desc limit 1". The subselect returns only the
>>> higest score and this is level 2.
>>>
>>> 2011/1/12 George Francis 
>>>
>>>  The following sql is intended to produce  a list of all unique SOURCEs,
 along with the corresponding LEVEL.NAME for LEVEL with highest SCORE
 value via SOURCELEVEL.

 I'd like to know why it doesn't return a SCORE value for SOURCE with id
 3, despite there being a SOURCELEVEL entry for it.

 Many thanks,


 drop table if exists source;

 drop table if exists sourcelevel;

 drop table if exists level;


 create table source ( source_id int );

 create table sourcelevel ( source_id int, level_id int);

 create table level ( level_id int, score int, name text );


 insert into source values (1), (2), (3);

 insert into level values ( 1, 10 ,'alpha' ), ( 2, 20, 'beta' ), ( 3, 15,
 'kappa' );

 insert into sourcelevel  values ( 1, 1 ), ( 1,2 ), ( 3,3 );


 select source.source_id, score, name from source

 left join ( select * from sourcelevel, level where sourcelevel.level_id
 = level.level_id order by score desc limit 1 )

 as temp on temp.source_id = source.source_id;

 --
 George

>>>
>>>
>>
>>
>> --
>> George Francis
>> e-mail: gfranc...@gmail.com
>>
>
>


-- 
George Francis
e-mail: gfranc...@gmail.com

Re: [SQL] joining one record according to max value

2011-01-12 Thread Jasmin Dizdarevic

There is probably a more elegant way, but this could help you.

select i.source_id, nm.name, i.mxscore from (
select sl.source_id, max(score) mxscore  from source s
inner join sourcelevel sl on s.source_id = sl.source_id
inner join level l on sl.level_id = l.level_id
group by 1
) i
inner join (
select name, max(score) as mxs from level
group by 1
) nm on i.mxscore = nm.mxs



2011/1/12 George Francis 

> Close!  but I need the name of the LEVEL with highest score, and I dont
> think I can get it this way.
> Thanks for trying though!
>
>
> On Wed, Jan 12, 2011 at 5:49 PM, Jasmin Dizdarevic <
> jasmin.dizdare...@gmail.com> wrote:
>
>> It's currently late, so excuse me if I'm wrong. Is this, what you like to
>> have?
>>
>> select sl.source_id, max(score)  from source s
>> inner join sourcelevel sl on s.source_id = sl.source_id
>> inner join level l on sl.level_id = l.level_id
>> group by 1
>>
>> This is the highest score per source_id.
>>
>> 2011/1/12 George Francis 
>>
>>> hmm, but if I try to constrain the inner query to the source_id of the
>>> outer query I get an error as follows:
>>>
>>> select source.source_id, score, name from source
>>> left join ( select * from sourcelevel, level where sourcelevel.level_id =
>>> level.level_id and sourcelevel.source_id = source.source_id order by score
>>> desc limit 1 )
>>>  as temp on temp.source_id = source.source_id;
>>>
>>> ERROR:  invalid reference to FROM-clause entry for table "source"
>>>
>>> LINE 14: ...l_id = level.level_id and sourcelevel.source_id =
>>> source.sou...
>>>
>>>   ^
>>>
>>> HINT:  There is an entry for table "source", but it cannot be referenced
>>> from this part of the query.
>>>
>>>
>>>
>>> ** Error **
>>>
>>>
>>> ERROR: invalid reference to FROM-clause entry for table "source"
>>>
>>> SQL state: 42P01
>>>
>>> Hint: There is an entry for table "source", but it cannot be referenced
>>> from this part of the query.
>>>
>>> Character: 601
>>>
>>>
>>> On Wed, Jan 12, 2011 at 5:35 PM, Jasmin Dizdarevic <
>>> jasmin.dizdare...@gmail.com> wrote:
>>>
 Because of the "score desc limit 1". The subselect returns only the
 higest score and this is level 2.

 2011/1/12 George Francis 

  The following sql is intended to produce  a list of all unique
> SOURCEs, along with the corresponding LEVEL.NAME for LEVEL with
> highest SCORE value via SOURCELEVEL.
>
> I'd like to know why it doesn't return a SCORE value for SOURCE with id
> 3, despite there being a SOURCELEVEL entry for it.
>
> Many thanks,
>
>
> drop table if exists source;
>
> drop table if exists sourcelevel;
>
> drop table if exists level;
>
>
> create table source ( source_id int );
>
> create table sourcelevel ( source_id int, level_id int);
>
> create table level ( level_id int, score int, name text );
>
>
> insert into source values (1), (2), (3);
>
> insert into level values ( 1, 10 ,'alpha' ), ( 2, 20, 'beta' ), ( 3,
> 15, 'kappa' );
>
> insert into sourcelevel  values ( 1, 1 ), ( 1,2 ), ( 3,3 );
>
>
> select source.source_id, score, name from source
>
> left join ( select * from sourcelevel, level where sourcelevel.level_id
> = level.level_id order by score desc limit 1 )
>
> as temp on temp.source_id = source.source_id;
>
> --
> George
>


>>>
>>>
>>> --
>>> George Francis
>>> e-mail: gfranc...@gmail.com
>>>
>>
>>
>
>
> --
> George Francis
> e-mail: gfranc...@gmail.com
>

Re: [SQL] joining one record according to max value

2011-01-12 Thread George Francis

thanks!  I think this is the best answer I will get.

On Wed, Jan 12, 2011 at 6:05 PM, Jasmin Dizdarevic <
jasmin.dizdare...@gmail.com> wrote:

> There is probably a more elegant way, but this could help you.
>
>  select i.source_id, nm.name, i.mxscore from (
> select sl.source_id, max(score) mxscore  from source s
>  inner join sourcelevel sl on s.source_id = sl.source_id
> inner join level l on sl.level_id = l.level_id
> group by 1
> ) i
> inner join (
> select name, max(score) as mxs from level
> group by 1
> ) nm on i.mxscore = nm.mxs
>
>
>
> 2011/1/12 George Francis 
>
>> Close!  but I need the name of the LEVEL with highest score, and I dont
>> think I can get it this way.
>> Thanks for trying though!
>>
>>
>> On Wed, Jan 12, 2011 at 5:49 PM, Jasmin Dizdarevic <
>> jasmin.dizdare...@gmail.com> wrote:
>>
>>> It's currently late, so excuse me if I'm wrong. Is this, what you like to
>>> have?
>>>
>>>  select sl.source_id, max(score)  from source s
>>> inner join sourcelevel sl on s.source_id = sl.source_id
>>> inner join level l on sl.level_id = l.level_id
>>> group by 1
>>>
>>> This is the highest score per source_id.
>>>
>>> 2011/1/12 George Francis 
>>>
 hmm, but if I try to constrain the inner query to the source_id of the
 outer query I get an error as follows:

  select source.source_id, score, name from source
 left join ( select * from sourcelevel, level where sourcelevel.level_id
 = level.level_id and sourcelevel.source_id = source.source_id order by 
 score
 desc limit 1 )
  as temp on temp.source_id = source.source_id;

  ERROR:  invalid reference to FROM-clause entry for table "source"

 LINE 14: ...l_id = level.level_id and sourcelevel.source_id =
 source.sou...

   ^

 HINT:  There is an entry for table "source", but it cannot be referenced
 from this part of the query.



 ** Error **


 ERROR: invalid reference to FROM-clause entry for table "source"

 SQL state: 42P01

 Hint: There is an entry for table "source", but it cannot be referenced
 from this part of the query.

 Character: 601


 On Wed, Jan 12, 2011 at 5:35 PM, Jasmin Dizdarevic <
 jasmin.dizdare...@gmail.com> wrote:

> Because of the "score desc limit 1". The subselect returns only the
> higest score and this is level 2.
>
> 2011/1/12 George Francis 
>
>  The following sql is intended to produce  a list of all unique
>> SOURCEs, along with the corresponding LEVEL.NAME for 
>> LEVEL with highest SCORE value via SOURCELEVEL.
>>
>> I'd like to know why it doesn't return a SCORE value for SOURCE with
>> id 3, despite there being a SOURCELEVEL entry for it.
>>
>> Many thanks,
>>
>>
>> drop table if exists source;
>>
>> drop table if exists sourcelevel;
>>
>> drop table if exists level;
>>
>>
>> create table source ( source_id int );
>>
>> create table sourcelevel ( source_id int, level_id int);
>>
>> create table level ( level_id int, score int, name text );
>>
>>
>> insert into source values (1), (2), (3);
>>
>> insert into level values ( 1, 10 ,'alpha' ), ( 2, 20, 'beta' ), ( 3,
>> 15, 'kappa' );
>>
>> insert into sourcelevel  values ( 1, 1 ), ( 1,2 ), ( 3,3 );
>>
>>
>> select source.source_id, score, name from source
>>
>> left join ( select * from sourcelevel, level where
>> sourcelevel.level_id = level.level_id order by score desc limit 1 )
>>
>> as temp on temp.source_id = source.source_id;
>>
>> --
>> George
>>
>
>


 --
 George Francis
 e-mail: gfranc...@gmail.com

>>>
>>>
>>
>>
>> --
>> George Francis
>> e-mail: gfranc...@gmail.com
>>
>
>


-- 
George Francis
e-mail: gfranc...@gmail.com

Re: [SQL] help needs in converting db2 function in postgresql.

[SQL] Implement PostgreSQL full text search

[SQL] INSERT/UPDATE .. RETURNING

[SQL] joining one record according to max value

Re: [SQL] joining one record according to max value

Re: [SQL] joining one record according to max value

Re: [SQL] joining one record according to max value

Re: [SQL] joining one record according to max value

Re: [SQL] joining one record according to max value

9 matches

Site Navigation

Mail list logo

Footer information