Re: [SQL] GROUP and ORDER BY

[Robins Tharakan]

Unless I overlooked something here, does this work ?

SELECT no, name, MAX(similarity(name, 'Tooneyvara')) AS sim
FROM vtown
WHERE similarity(name, 'Tooneyvara') > 0.4
GROUP BY no, name
ORDER BY sim DESC

--
Robins Tharakan

On 11/08/2011 02:50 AM, Tarlika Elisabeth Schmitz wrote:

Hello,

I would like to GROUP the result by one column and ORDER it by another:

SELECT
 no, name, similarity(name, 'Tooneyvara') AS s
 FROM vtown
 WHEREsimilarity(name, 'Tooneyvara')>  0.4
 ORDER BY s DESC

Result:

1787"Toomyvara"   0.5
1787"Toomevara"   0.4
1188"Toonybara"   0.4


Desired result:

1787"Toomyvara"   0.5
1188"Toonybara"   0.4

Gets rid of the  duplicate "no" keeping the spelling with the greater
similarity and presents the remaining result ordered by similarity.


My solution:

SELECT * FROM
(
SELECT DISTINCT ON (no)
 no, name,
 similarity(name, 'Tooneyvara') AS sim
 FROM vtown
 WHERE similarity(name, 'Tooneyvara')>  0.4
 ORDER BY no, sim DESC
) AS x
ORDER BY sim


Is that the best way to achieve this result?


--
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] GROUP and ORDER BY

[Robins Tharakan]

Hmmm... Missed that!

I think you are looking for the feature that was introduced in 
PostgreSQL 9.1 where you could have a non-group-by column in the select 
list, but only if the group-by has a pkey to identify the actual row.


http://www.postgresql.org/docs/9.1/static/release-9-1.html
(Search for GROUP BY)

--
Robins Tharakan

On 11/08/2011 03:29 PM, Tarlika Elisabeth Schmitz wrote:

Thank you for yuor suggestion, Robins. Unfortunately, it does not work;
this returns:
1787"Toomyvara"   0.5
1787"Toomevara"   0.4
1188"Toonybara"   0.4
because while column "no" is identical, "name" isn't and you're
grouping by both of them.


--
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] MS-SQL to PostgreSql

[Robins Tharakan]

Hi,

What all have you tried?
What are you getting stuck at?
Let us see some samples and may be someone could provide some input.

--
Robins

On 03/26/2012 01:19 PM, Rehan Saleem wrote:

hi,
i am trying to convert this mssql store procedure to postgresql function
but it is not giving me the desired output ,this function is returning a
table and you dont need to worry about what it is returning all i
concern the body part of the function how to transform the ms-sql code
into postgresql code, i shall be very thankful if some one convert this
procedure into function,

ALTER PROCEDURE [dbo].[sp_GetSitesByTFID]
@UserDataDetailId varchar(50),
@KBId varchar(50),
@bpOverlap varchar(50),
@Chr varchar(50),
@CentreDistance varchar(50)='',
@TotalMatched varchar(50) output
AS
BEGIN

DECLARE @sql nvarchar(500);


if (@CentreDistance='')
set @CentreDistance = 1
set @sql = 'select Chr_U, Start_U, End_U, RegionSize_U, Chr_KB,
Start_KB, End_KB, RegionSize_KB, '
set @sql += ' bpOverlap, CentreDistance from vwChrCompareSiteswhere
UserDataDetailId=' + @UserDataDetailId
set @sql += ' and bpOverlap>=' + @bpOverlap
set @sql += ' AND KBId=' + @KBId
if @Chr<>'All' and ISNULL(@Chr,'')<>''
set @sql += ' AND Chr_U=''' + @Chr +
if (@CentreDistance<>'')
set @sql += ' AND (CentreDistance<=' + @CentreDistance + ' or ' +
@CentreDistance + '=1) '
set @sql += ' Order by Chr_U, Start_U'

exec(@sql)
set @TotalMatched = @@ROWCOUNT
END





smime.p7s
Description: S/MIME Cryptographic Signature

Re: [SQL] how to concatenate in PostgreSQL

[Robins Tharakan]

Hi,

Probably you're looking for these set of articles.

http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Microsoft_SQL_Server

The second article (by Ethan) has good small hints for things such as 
the query that you ask in this thread, when migrating from MSSQL to 
PostgreSQL.


--
Robins

On 03/24/2012 05:13 PM, Rehan Saleem wrote:

hi ,
how can we concatinate these lines and execute sql command
setsql = 'select user,username, firstname '
set sql += ' lastname, cardno from table1 where userid=' + 5
exec(sqi)
where 5 is the userid from table1
thanks





smime.p7s
Description: S/MIME Cryptographic Signature

Re: [SQL] MS-SQL to PostgreSql

[Robins Tharakan]

Hi Rehan,

p.s.: Please send all mails to list, so that all can see the response.

The function returns for all, probably because you see to be RETURN'ing 
the values before any processing. In the MSSQL you generate the SQL in a 
string and then execute the SQL. However, for some reason you seem to be 
RETURNing the output immediately after the 'IF center_distance IS NULL' 
condition.


Probably the RETURN needs to happen 'after' all the chr checks.

--
Robins

On 03/26/2012 03:48 PM, Rehan Saleem wrote:

hi ,
i have tried this but it is not working correctly , when i pass it a
value which is present in the chr column chr1 it show all chr values not
only the chr1 values .

CREATE OR REPLACE FUNCTION getsitesbytfid(user_datadetailid int, kb_id
int,bp_overlap int,chr_ varchar ,centre_distance int)
RETURNS table(chr__u varchar,start__u int,end__u int,region_size_u
int,chr__kb varchar,start__kb int ,end__kb int,region_size_kb
int,bp__overlap int,centre__distance int)
as
$BODY$
DECLARE sql varchar ;
BEGIN


if (centre_distance is NULL) THEN
centre_distance := 1;
end if;


return query select chr_u, start_u, end_u, regionsize_u, chr_kb,
start_kb, end_kb, regionsize_kb,
bpoverlap, centredistance from vwchrcomparesites where userdatadetailid=
+ user_datadetailid
and bpoverlap>= + bp_overlap
and kbid= + kb_id ;

if chr_<>'all' and COALESCE(chr_,'')<>'' then
chr_:=chr_ ;
end if;
if centre_distance IS NULL THEN
centre_distance := ' and (centredistance<=' + centre_distance + ' or ' +
centre_distance + '=1) '
|| ' order by chr_u, start_u';


--exec sql;

end if;

END;
$BODY$
language plpgsql;

*From:* Robins Tharakan 
*To:* pgsql-sql@postgresql.org
*Sent:* Monday, March 26, 2012 3:03 PM
*Subject:* Re: [SQL] MS-SQL to PostgreSql

Hi,

What all have you tried?
What are you getting stuck at?
Let us see some samples and may be someone could provide some input.

--
Robins

On 03/26/2012 01:19 PM, Rehan Saleem wrote:
 > hi,
 > i am trying to convert this mssql store procedure to postgresql function
 > but it is not giving me the desired output ,this function is returning a
 > table and you dont need to worry about what it is returning all i
 > concern the body part of the function how to transform the ms-sql code
 > into postgresql code, i shall be very thankful if some one convert this
 > procedure into function,
 >
 > ALTER PROCEDURE [dbo].[sp_GetSitesByTFID]
 > @UserDataDetailId varchar(50),
 > @KBId varchar(50),
 > @bpOverlap varchar(50),
 > @Chr varchar(50),
 > @CentreDistance varchar(50)='',
 > @TotalMatched varchar(50) output
 > AS
 > BEGIN
 >
 > DECLARE @sql nvarchar(500);
 >
 >
 > if (@CentreDistance='')
 > set @CentreDistance = 1
 > set @sql = 'select Chr_U, Start_U, End_U, RegionSize_U, Chr_KB,
 > Start_KB, End_KB, RegionSize_KB, '
 > set @sql += ' bpOverlap, CentreDistance from vwChrCompareSiteswhere
 > UserDataDetailId=' + @UserDataDetailId
 > set @sql += ' and bpOverlap>=' + @bpOverlap
 > set @sql += ' AND KBId=' + @KBId
 > if @Chr<>'All' and ISNULL(@Chr,'')<>''
 > set @sql += ' AND Chr_U=''' + @Chr +''''
 > if (@CentreDistance<>'')
 > set @sql += ' AND (CentreDistance<=' + @CentreDistance + ' or ' +
 > @CentreDistance + '=1) '
 > set @sql += ' Order by Chr_U, Start_U'
 >
 > exec(@sql)
 > set @TotalMatched = @@ROWCOUNT
 > END
 >







smime.p7s
Description: S/MIME Cryptographic Signature

[SQL] Clarity on how LOCK interacts with INHERIT

[Robins Tharakan]

Hi,

The following when run in PostgreSQL 9.2.x seems to tell that if I have two
tables A and B, such that if B inherits A, then, even if I don't have any
rights on A, I can get an ACCESS EXCLUSIVE LOCK on Table A via Table B.

This isn't necessarily wrong, I just want be sure that this is what
PostgreSQL allows us to do.

Sample SQL:

postgres=# CREATE SCHEMA lock_schema1;
CREATE SCHEMA
postgres=# SET search_path = lock_schema1;
SET
postgres=# CREATE ROLE lock_rol5;
CREATE ROLE
postgres=# CREATE TABLE lock_tbl5 (a BIGINT);
CREATE TABLE
postgres=# CREATE TABLE lock_tbl6 (b BIGINT) INHERITS (lock_tbl5);
CREATE TABLE
postgres=# GRANT USAGE ON SCHEMA lock_schema1 TO lock_rol5;
GRANT
postgres=# GRANT ALL ON TABLE lock_tbl6 TO lock_rol5;
GRANT
postgres=# REVOKE ALL ON TABLE lock_tbl5 FROM lock_rol5;
REVOKE
postgres=# SET ROLE lock_rol5;
SET
postgres=> SET search_path=lock_schema1;
SET
postgres=> BEGIN TRANSACTION;
BEGIN
postgres=> LOCK TABLE ONLY lock_tbl6 IN access EXCLUSIVE MODE;
LOCK TABLE
postgres=> ROLLBACK;
ROLLBACK
postgres=> BEGIN TRANSACTION;
BEGIN
postgres=> LOCK TABLE lock_tbl6 * IN access EXCLUSIVE MODE;
LOCK TABLE
postgres=> ROLLBACK;
ROLLBACK
postgres=> BEGIN TRANSACTION;
BEGIN
postgres=> LOCK TABLE lock_tbl5 IN access EXCLUSIVE MODE;
ERROR:  permission denied for relation lock_tbl5
STATEMENT:  LOCK TABLE lock_tbl5 IN access EXCLUSIVE MODE;
ERROR:  permission denied for relation lock_tbl5
postgres=> ROLLBACK;
ROLLBACK
postgres=> RESET ROLE;
RESET
postgres=# DROP TABLE lock_tbl6;
DROP TABLE
postgres=# DROP TABLE lock_tbl5;
DROP TABLE
postgres=# REVOKE ALL ON SCHEMA lock_schema1 FROM lock_rol5;
REVOKE
postgres=# DROP ROLE lock_rol5 ;
DROP ROLE
postgres=#


Thanks

--
Robins Tharakan

Re: [SQL] Quick question re foreign keys.

[Robins Tharakan]

Forgive my butting in, but frankly, most of the times, whenever I find
myself in a very 'exceptional problem' such as this one, I always end up
questioning the basic design due to which I am stuck in the first place.

Paul, it seems that probably there is a basic design issue here.

All the best :)

Robins

On 10/24/07, Paul Lambert <[EMAIL PROTECTED]> wrote:
>
> Paul Lambert wrote:
> >
> > It's marked not null as a result of being part of the primary key for
> > that table which I can't really get around.
> >
> > I can get away with not having the foreign key though, so I'll have to
> > go down that path.
> >
> > Cheers,
> > P.
> >
>
> Ignore this whole thread actually.
>
> I need to rethink some of my design.
>
> --
> Paul Lambert
> Database Administrator
> AutoLedgers
>
> ---(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] Helper aggregate function

[Robins Tharakan]

Please correct me if I am wrong, but as the last few lines of the first
section in the given document says, you can use $n only for values and not
for identifiers.

http://www.postgresql.org/docs/8.3/static/xfunc-sql.html

And one more thing, may be you would want to use a user-defined return type
instead of void for this function.

*Robins*

On Feb 3, 2008 5:14 PM, sergey kapustin <[EMAIL PROTECTED]> wrote:

> Hello!
> Can anyone tell me how i do this properly?
>
> create or replace function agg(varchar,varchar) returns void as $func$
> select $1,count(*) from $2 group by $1 order by $1;
> $func$
> Language SQL;
>
> Right now this wives me "ERROR:  syntax error at or near "$2" at character
> 97
> select $1,count(*) from $2 group by $1 order by $1;"
>
> Thank you!
>
>
>
>

Re: [SQL] TG_TABLE_NAME as identifier

[Robins Tharakan]

Hi,

I am not sure if this'd help :

1. Are you sure that the sequence and the tablename have the same name ?
[The insert statement is seeing the insert target identifier as a variable]

2. In case you need to run the  [INSERT INTO '|| TG_TABLE_NAME ||' SELECT
NEW.* ] statement you could always use EXECUTE to run concatenated strings.

3. Unrelated, but as an advice, I always recommend giving field names while
inserting and intentionally try and and avoid insert statements such as
INSERT INTO xxx SELECT * .

*Robins*


-- Forwarded message --
From: Tiziano Slack <[EMAIL PROTECTED]>
Date: Feb 5, 2008 2:15 PM
Subject: [SQL] TG_TABLE_NAME as identifier
To: pgsql-sql@postgresql.org


Hello everybody! I'm newbie to plpgsql syntax. Can anyone tell where I'm
getting wrong?

CREATE OR REPLACE FUNCTION tr_audit() RETURNS TRIGGER AS $tr_audit$
BEGIN
IF (TG_OP = 'UPDATE') THEN
...
NEW.id = nextval(TG_TABLE_NAME || '_id_seq'::regclass);
INSERT INTO TG_TABLE_NAME SELECT NEW.*;
ELSIF (TG_OP = 'INSERT') THEN
...
END IF;

RETURN NULL;
END;
$tr_audit$ LANGUAGE 'plpgsql';

returns

ERROR:  syntax error at or near "$1"
LINE 1: INSERT INTO  $1  SELECT  $2 .*
 ^
QUERY:  INSERT INTO  $1  SELECT  $2 .*
CONTEXT:  SQL statement in PL/PgSQL function "tr_audit" near line 8

I tried some variants using INSERT INTO '|| TG_TABLE_NAME ||' SELECT NEW.*
and quote_ident(TG_TABLE_NAME) with no luck! Unfortunately searches on the
Net and old threads on this forum didn't help me.

Hope someone can do this!

Thanks in advance,

Tiziano.

--
Windows Live Spaces Push the Button! Crea il tuo blog e condividi le tue
esperienze col mondo! 

Re: [SQL] Multiple postgresql functions in a single transaction

[Robins Tharakan]

> It can be done, but it depends on how you are generating the value in the
> first function.
> If you sequences though you may have to take care of reverting it
> yourself.
>
> *Robins*
>
>
> -- Forwarded message --
> From: Jyoti Seth <[EMAIL PROTECTED]>
> Date: Feb 6, 2008 11:51 AM
> Subject: [SQL] Multiple postgresql functions in a single transaction
> To: pgsql-sql@postgresql.org
>
>
> Hi,
>
> I have two postgresql functions. One function is calling another function
> for certain value. I want that these two functions work under single
> transaction so that even if the value gets generated in the second
> function
> and the first function that calls the second function fails. Then the
> value
> generated in the second function should also roll back.
>
> Please let me know if we can execute two functions of postgresql in a
> single
> transaction.
>
> Thanks,
> Jyoti Seth
>
>
>
>
>
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>
>

[SQL] UPDATE with ORDER BY

[Robins Tharakan]

Hi,

I know this kind of a question is asked earlier, but I couldn't find an
answer there (in the previous round of posting).

Instead of wanting to update the first record in an UPDATE .. ORDER BY
condition, (because of triggers that act downward) what I want is that all
records be updated, but in a given order, for e.g. ascending on date.

Is a FOR LOOP my best bet or is something like UPDATE  ORDER BY in the
offing ?

Thanks
*Robins*

Re: [SQL] postgresql function not accepting null values in select statement

[Robins Tharakan]

>
> Hi,
>
> When you pass non-null values in p_statecd the result should work fine,
> but when you pass NULL in p_statecd ... the equal operator stops to work as
> you as expect it to.
>
> Please see this documentation:
> http://www.postgresql.org/docs/8.2/interactive/functions-comparison.html
> from where I quote: "Do *not* write *expression* = NULL because NULL is
> not "equal to" NULL. (The null value represents an unknown value, and it
> is not known whether two unknown values are equal.) This behavior conforms
> to the SQL standard."
>
> As the document suggests you may want to try this way out:
>
>  WHERE f.statecd IS NOT DISTINCT FROM p_statecd
>
> This would take care of both NULL and non-NULL values.
>
> *Robins*
>
>
> -- Forwarded message --
> From: Jyoti Seth <[EMAIL PROTECTED]>
> Date: Fri, Feb 22, 2008 at 2:52 PM
> Subject: Re: [SQL] postgresql function not accepting null values in select
> statement
> To: Richard Huxton <[EMAIL PROTECTED]>
> Cc: pgsql-sql@postgresql.org
>
>
> Hi,
>
> I have a the following procedure
>
> CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer)
>  RETURNS SETOF t_functionaries AS
> $BODY$
> DECLARE rec t_functionaries%ROWTYPE;
> begin
>   FOR rec IN
>SELECT f.functionaryid, f.category,f.description
>FROM functionaries f
> where f.statecd=p_statecd
>
>LOOP
>return next rec;
>END LOOP;
>   return;
>   end;
> $BODY$
>  LANGUAGE 'plpgsql' VOLATILE;
>
> In the functionaries table statecd is a null field. When I pass some
> integer
> value to the above procedure it works correctly but if I pass null value
> in
> p_statecd it doesn't show anything whereas it has values and if I write
> the
> select statement separately it gives values
>
> Thanks,
> Jyoti
>
> -Original Message-
> From: Richard Huxton [mailto:[EMAIL PROTECTED]
> Sent: Friday, February 22, 2008 2:35 PM
> To: Jyoti Seth
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] postgresql function not accepting null values in select
> statement
>
> Jyoti Seth wrote:
> >
> > If I pass null value as the parameter of postgresql function, which is
> used
> > in the where clause of select statement is not functioning properly.
>
> Either:
>
> 1. You're talking about frooble(), in which case it's supposed to do that.
>
> or
>
> 2. You'll need to tell us what function it is, how you're using it and
> what you think should happen.
>
> My guess is that you're getting a null as the result and that's not
> doing what you'd expect in your where clause.
>
>
> --
>   Richard Huxton
>   Archonet Ltd
>
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>
>

Re: [SQL] postgresql function not accepting null values inselect statement

[Robins Tharakan]

http://www.postgresql.org/docs/current/static/functions-comparison.html

This document states this:
Lets assume:
A = NULL
B = 10
C = NULL

SELECT 1 WHERE A = B returns no rows
SELECT 1 WHERE A = C returns no rows (even though both A and C are NULL)
SELECT 1 WHERE A IS NOT DISTINCT FROM C returns 1 row.

essentially the third SQL statement works because it is equivalent to this:

SELECT 1 WHERE (A IS NULL AND C IS NULL) OR (A = C)

*Robins*


On Fri, Feb 22, 2008 at 10:00 PM, johnf <[EMAIL PROTECTED]> wrote:

> On Friday 22 February 2008 01:35:47 am Bart Degryse wrote:
> > Can you try this...
> >
> > CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer)
> >   RETURNS SETOF t_functionaries AS
> > $BODY$
> > DECLARE
> >   rec t_functionaries%ROWTYPE;
> > BEGIN
> >   FOR rec IN (
> > SELECT f.functionaryid, f.category, f.description
> > FROM functionaries f
> > WHERE f.statecd IS NOT DISTINCT FROM p_statecd)
> >   LOOP
> > return next rec;
> >   END LOOP;
> >   return;
> > END;
> > $BODY$
> > LANGUAGE 'plpgsql' VOLATILE;
> >
> a newbie question.  Could you explain why yours works?  I don't understand
> how
> it works if p_statecd = NULL
>
>
> --
> John Fabiani
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>

Re: [SQL] postgresql function not accepting null values inselect statement

[Robins Tharakan]

>
> What version of PostGreSQL are you using ?
> Are you sure there was no typing error ? This SQL should work in the most
> recent version of PG.( at least version 8.1 onwards)
>
> *Robins*
>
>
> On Mon, Feb 25, 2008 at 10:50 AM, Jyoti Seth <[EMAIL PROTECTED]>
> wrote:
>
> > I have tried this, but it is showing following error:
> > ERROR: syntax error at or near "DISTINCT"
> > SQL state: 42601
> >
> > Thanks,
> > Jyoti
> >
> >
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:
> > [EMAIL PROTECTED]
> > On Behalf Of johnf
> > Sent: Friday, February 22, 2008 10:01 PM
> > To: pgsql-sql@postgresql.org
> > Subject: Re: [SQL] postgresql function not accepting null values
> > inselect
> > statement
> >
> > On Friday 22 February 2008 01:35:47 am Bart Degryse wrote:
> > > Can you try this...
> > >
> > > CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer)
> > >   RETURNS SETOF t_functionaries AS
> > > $BODY$
> > > DECLARE
> > >   rec t_functionaries%ROWTYPE;
> > > BEGIN
> > >   FOR rec IN (
> > > SELECT f.functionaryid, f.category, f.description
> > > FROM functionaries f
> > > WHERE f.statecd IS NOT DISTINCT FROM p_statecd)
> > >   LOOP
> > > return next rec;
> > >   END LOOP;
> > >   return;
> > > END;
> > > $BODY$
> > > LANGUAGE 'plpgsql' VOLATILE;
> > >
> > a newbie question.  Could you explain why yours works?  I don't
> > understand
> > how
> > it works if p_statecd = NULL
> >
> >
> > --
> > John Fabiani
> >
> > ---(end of broadcast)---
> > TIP 5: don't forget to increase your free space map settings
> >
> >
> > ---(end of broadcast)---
> > TIP 4: Have you searched our list archives?
> >
> >   http://archives.postgresql.org
> >
>
>

Re: [SQL] rule for update view that updates/inserts into 2 tables

[Robins Tharakan]

Hi Chad,

(Pardon me if I am shooting the stars here...)

Don't you think that on each update, you would be creating a new row that
satisfies that very given condition for the view ?

By that I mean that when you do a 'UPDATE... WHERE my_table_id=1' the RULE
now inserts another row with my_table_id = 1... which now makes two rows
that satisfy the criteria for the view.

The second time you run the update, the RULE inserts a row (in the
my_audit_table) for each row found (on the second run it'd be two rows) ...
and then so on .

Therefore, you probably want to use this CREATE RULE query instead...

CREATE OR REPLACE VIEW my_view AS
 SELECT t.my_table_id, t.a, t.b, au.audit_id, au.c
   FROM my_table t, my_audit_table au
  WHERE t.my_table_id = au.my_table_id
  AND au.audit_id = (SELECT max(audit_id) FROM my_audit_table WHERE
au.my_table_id = my_audit_table.my_table_id);

Of course this brings us into another problem that the INSERT / UPDATE
statements bomb because of the aggregate that is now there in the view...
and then I am drawing a blank here !

(Note: As mentioned in PG Docs, I have already tried creating a blanket DO
NOTHING rule coupled with Chad's rule as a DO ALSO rule ... but that doesn't
work either)

Anyone else with some ideas ?

*Robins*

On Mon, Apr 14, 2008 at 10:17 PM, Chad Showalter <[EMAIL PROTECTED]>
wrote:

>  I would like to create a rule that, by updating a view, allows me to
> update one table and insert into another.
>
>
>
> The following example illustrates what I'm trying to do:
>
>
>
> --Create Tables
>
> CREATE TABLE my_table
>
> (
>
> my_table_id serial,
>
> a character varying(255),
>
> b character varying(255),
>
> CONSTRAINT my_table_id_pk PRIMARY KEY (my_table_id)
>
> );
>
>
>
> CREATE TABLE my_audit_table
>
> (
>
> audit_id serial,
>
> my_table_id int,
>
> c character varying(255),
>
> CONSTRAINT audit_id_pk PRIMARY KEY (audit_id)
>
> );
>
>
>
> --Create View
>
> CREATE OR REPLACE VIEW my_view AS
>
> SELECT
>
> t.my_table_id,
>
> t.a,
>
> t.b,
>
> au.audit_id,
>
> au.c
>
> FROM
>
> my_table t, my_audit_table au
>
> WHERE
>
> t.my_table_id = au.my_table_id;
>
>
>
> --Create Rules
>
> CREATE OR REPLACE RULE insert_to_my_view AS
>
> ON INSERT TO my_view
>
> DO INSTEAD(
>
> INSERT INTO my_table (a,b)
>
> VALUES(new.a, new.b);
>
> INSERT INTO my_audit_table(my_table_id, c)
>
> VALUES
>
> (currval('my_table_my_table_id_seq'), new.c);
>
> );
>
>
>
> CREATE OR REPLACE RULE update_my_view AS
>
> ON UPDATE TO my_view DO INSTEAD
>
>  ( UPDATE my_table SET
>
> a = new.a,
>
> b = new.b
>
> WHERE
>
> my_table_id = old.my_table_id;
>
> INSERT INTO my_audit_table
>
>  (my_table_id,
>
>  c)
>
> VALUES
>
>  (new.my_table_id,
>
>  new.c);
>
> );
>
>
>
> --The insert statement below inserts one row into my_table, and one row
> into my_audit_table
>
> --(This works the way I would like)
>
> insert into my_view(a,b,c) values('a contents','b contents', 'c
> contents');
>
>
>
> --The update statement below doesn't work the way I want.
>
> --What I would like this to do is to update one row in my_table, and
> insert
>
> --one row into my_audit table.  It does the update fine, but the insert to
> my_audit_table
>
> --doesn't work as I had anticipated.
>
> update my_view set a = 'new a contents', b = 'new b contents', c  = 'new c
> contents' where my_table_id = 1;
>
>
>
>
>
>
>
>
>
> If I execute the above update statement multiple times, multiple rows will
> be
>
> inserted with each call after the first call.
>
>
>
> Specifically,
>
> · after the first call, 1 row is inserted
>
> · after the second call, 2 rows are inserted
>
> · after the third call, 4 rows are inserted
>
> · after the fourth call, 8 rows are inserted... and so on
>
>
>
> The problem is due to the INSERT in the update_my_view rule:
>
>
>
> INSERT INTO my_audit_table
>
>  (my_table_id,
>
> c)
>
> VALUES
>
>  (new.my_table_id,
>
>  new.c);
>
>
>
> Apparently, "new.my_table_id" in this case references more than one row,
> if more than one row with
>
> the given id already exists in my_audit_table.
>
>
>
> How do I accomplish what I want to accomplish here?  I'd prefer not to use
> a sp.
>
>
>
> Thanks,
>
> Chad
>
>
>

Re: [SQL] Add a ROWCOUNT to the output of a select.

[Robins Tharakan]

While we could always check for the query performance reasons, I rather
think that this is an overkill for the purpose of mere line numbers.

If such queries don't change frequently, you could be better off using a
simple function that instead adds a 'rownumber' field to the output of the
inner SQL query. The 'rownumber' could instead be calculated by simply
incrementing it within a FOR loop for each row.

*Robins*

On Wed, May 14, 2008 at 5:24 AM, Gavin 'Beau' Baumanis <
[EMAIL PROTECTED]> wrote:

> Hi Everyone,
>
> After spending some time searching through our good friend Mr. Google and
> the mailing list I found a post that provided me with a query that does just
> what I need.
>
> However, the query takes FOREVER and although this is stated in the
> original mail I thought I would ask if there was any advice that you might
> be able to provide to speed things up a little.
>
> And while the query does return over 27,000 rows in my case, I didn't
> expect it to take 9 minutes and 11 seconds!
>
> Please find the query below - and of course - thanks in advance for any
> assistance you might be able to provide me with!
>
> select
>(select
> count(*)
> from
> myTable as myCount
> where
> myCount.contactdate <= myTable.contactdate
>) as rownum,
>contactdate
> from
>myTable
> where
>contactdate > '2007-06-30 23:59:59'
> order by
>contactdate;
>
>
> -Gavin
>
> --
> 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] Add a ROWCOUNT to the output of a select.

[Robins Tharakan]

Oops!
Of course, I meant a sequence.

*Robins*

On Wed, May 14, 2008 at 2:10 PM, Harald Fuchs <[EMAIL PROTECTED]>
wrote:

> In article <[EMAIL PROTECTED]>,
> "Robins Tharakan" <[EMAIL PROTECTED]> writes:
>
> > While we could always check for the query performance reasons, I
> > rather think that this is an overkill for the purpose of mere line
> > numbers.
>
> > If such queries don't change frequently, you could be better off
> > using a simple function that instead adds a 'rownumber' field to the
> > output of the inner SQL query. The 'rownumber' could instead be
> > calculated by simply incrementing it within a FOR loop for each row.
>
> I think a sequence is much simpler:
>
> create temp sequence tmp;
> select nextval('tmp') as rownum,
> contactdate
> from
>myTable
> where
>contactdate > '2007-06-30 23:59:59'
> order by
>contactdate;
>
>
> --
> 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] Substract queries

[Robins Tharakan]

Probably you are looking for EXCEPT.

SELECT * FROM Tbl1 WHERE a=1
EXCEPT
SELECT * FROM tbl2 WHERE a=1 and b=1;

http://www.postgresql.org/docs/8.3/interactive/sql-select.html

Regards,
*Robins Tharakan*

-- Forwarded message --
From: Nacef LABIDI <[EMAIL PROTECTED]>
Date: Thu, May 22, 2008 at 8:45 PM
Subject: [SQL] Substract queries
To: pgsql-sql@postgresql.org


Hi all,

I was wondering how can I substract result between select queries. I mean I
want to issue a query that does this :
(select * from mytable where condition1) - (select * from mytable where
condition2)

Thanks to all

Nacef

[SQL] Unnecessary repeat condition for a self inner join

[Robins Tharakan]

Hi,

I am not sure if this is a simple (... stupid) question but I just wasted
two hours optimizing a query, so I thought I should drop in to ask.

The only difference between query1 and query2 (below) is that despite an
explicit INNER JOIN, I have repeated the same condition for n2 (as given for
n1) and this makes a whole lot of difference in performance (since it now
uses the same index for n2 that it is using for n1).

In case of an INNER JOIN, shouldn't the second condition (in Query2) be
unnecessary ?
Or am I being unreasonable in this expectation ?

Regards,
*Robins Tharakan*

p.s.: The query below is just a simplification, and provides only EXPLAIN,
but I think an EXPLAIN ANALYSE should be unnecessary here. In case anyone
still needs it, please do tell.

*Query 1*:

SELECT n1.scheme_code
FROM nav n1
INNER JOIN nav n2 ON n1.scheme_code = n2.scheme_code
WHERE n1.scheme_code BETWEEN 100 AND 200

"Merge Join  (cost=903471.23..10248343.37 rows=622920912 width=4)"
"  Merge Cond: (n1.scheme_code = n2.scheme_code)"
"  ->  Sort  (cost=110929.32..111458.60 rows=211712 width=4)"
"Sort Key: n1.scheme_code"
"->  Bitmap Heap Scan on nav n1  (cost=8623.86..92201.54 rows=211712
width=4)"
"  Recheck Cond: ((scheme_code >= 100) AND (scheme_code <=
200))"
"  ->  Bitmap Index Scan on pk_fs_nav  (cost=0.00..8570.94
rows=211712 width=0)"
"Index Cond: ((scheme_code >= 100) AND (scheme_code <=
200))"
"  ->  Sort  (cost=792541.91..805391.17 rows=5139702 width=4)"
"Sort Key: n2.scheme_code"
"->  Seq Scan on nav n2  (cost=0.00..131799.02 rows=5139702
width=4)"


*Query 2*:

SELECT n1.scheme_code
FROM nav n1
INNER JOIN nav n2 ON n1.scheme_code = n2.scheme_code
WHERE n1.scheme_code BETWEEN 100 AND 200
AND n2.scheme_code BETWEEN 100 AND 200


"Merge Join  (cost=221858.63..607790.72 rows=25659043 width=4)"
"  Merge Cond: (n2.scheme_code = n1.scheme_code)"
"  ->  Sort  (cost=110929.32..111458.60 rows=211712 width=4)"
"Sort Key: n2.scheme_code"
"->  Bitmap Heap Scan on nav n2  (cost=8623.86..92201.54 rows=211712
width=4)"
"  Recheck Cond: ((scheme_code >= 100) AND (scheme_code <=
200))"
"  ->  Bitmap Index Scan on pk_fs_nav  (cost=0.00..8570.94
rows=211712 width=0)"
"Index Cond: ((scheme_code >= 100) AND (scheme_code <=
200))"
"  ->  Sort  (cost=110929.32..111458.60 rows=211712 width=4)"
"Sort Key: n1.scheme_code"
"->  Bitmap Heap Scan on nav n1  (cost=8623.86..92201.54 rows=211712
width=4)"
"  Recheck Cond: ((scheme_code >= 100) AND (scheme_code <=
200))"
"  ->  Bitmap Index Scan on pk_fs_nav  (cost=0.00..8570.94
rows=211712 width=0)"
"Index Cond: ((scheme_code >= 100) AND (scheme_code <=
200))"

Re: [SQL] Unnecessary repeat condition for a self inner join

[Robins Tharakan]

> While the optimizer theoretically could deduce the extra restriction
> condition, it doesn't attempt to.  It's extremely unclear that the extra
> cycles to look for such cases would be repaid on average, because cases
> like this aren't that common.  The current state of affairs is that
> the system will deduce implied equality conditions, but not implied
> inequality conditions.


One good thing is that the equality conditions are taken care of. But I fail
to understand why do you believe that the second case is rare. I think the
optimizer would (in all self-join inequality conditions) tend towards a
table scan, which for a large table is a disaster. (Of course, the index
scan would help only if the result-set is small)

Besides, I did a simple test and although you are right about the optimizer
deducing implied equality conditions, this holds true only for a direct
join. In the second query, the optimizer recommends a table scan even for a
simple IN() condition.

Is that normal ?

Regards,
*Robins Tharakan*

Query 1:

SELECT n1.scheme_code
FROM nav n1
INNER JOIN nav n2 ON n1.scheme_code = n2.scheme_code
WHERE n1.scheme_code = 290

"Nested Loop  (cost=0.00..16147232.47 rows=4796100 width=4)"
"  ->  Index Scan using nav__schemecode_date_lookup3b on nav n1
(cost=0.00..7347.91 rows=2190 width=4)"
"Index Cond: (scheme_code = 290)"
"  ->  Index Scan using nav__schemecode_date_lookup3b on nav n2
(cost=0.00..7347.91 rows=2190 width=4)"
"Index Cond: (290 = scheme_code)"


Query 2:

SELECT n1.scheme_code
FROM nav n1
INNER JOIN nav n2 ON n1.scheme_code = n2.scheme_code
WHERE n1.scheme_code IN (1, 2)

"Hash Join  (cost=206004.00..431864.83 rows=10720451 width=4)"
"  Hash Cond: (n1.scheme_code = n2.scheme_code)"
"  ->  Bitmap Heap Scan on nav n1  (cost=139.62..13663.13 rows=4378
width=4)"
"Recheck Cond: (scheme_code = ANY ('{1,2}'::integer[]))"
"->  Bitmap Index Scan on nav__schemecode_date_lookup3b
(cost=0.00..138.53 rows=4378 width=0)"
"  Index Cond: (scheme_code = ANY ('{1,2}'::integer[]))"
"  ->  Hash  (cost=112078.06..112078.06 rows=5395306 width=4)"
"->  Seq Scan on nav n2  (cost=0.00..112078.06 rows=5395306
width=4)"