[SQL] ROW_NUMBER alias
Hi,
I needed ROW_NUMBER() in PostGresql and I did find the 'temporary sequence'
method as a workaround and i think it at least gets the job done relatively
well, ... so no problems there.
Its just that from a usability point of view, isn't it better that we
provide some kind of an aliasing mechanism here that allows a new user to
(unknowingly but) implicitly use a temporary sequence rather than make him
use SubQuery with a COUNT(*) and a comparison operator (with disastrous
performance) instead ??
So for a new user :
A query such as this :
SELECT ROW_NUMBER() AS row_number , a, b, c
FROM table
WHERE table_id = 973
ORDER BY record_date;
is internally interpreted by the planner as :
CREATE TEMP SEQUENCE rownum;
SELECT nextval('rownum') AS row_number , t.a, t.b, t.c
FROM (
SELECT a, b, c
FROM table
WHERE table_id = 973
ORDER BY record_date
) t;
DROP SEQUENCE rownum;
Any ideas ?
(Of what I remember, I think till recently PostgreSql internally replaced
'MAX(x)' queries with a 'ORDER BY x DESC LIMIT 1' implicitly)
--
Robins
Re: [SQL] ROW_NUMBER alias
Hi Stefan,
Well that was just an example. That table had a primary key on (id, date).
By bad then, coz I guess I should have clarified that earlier.
But what I meant from the example was that it is trivial for the parser to
automatically put an enveloping SELECT to add a ROW_NUMBER() field to any
user given query.
Regards,
Robins Tharakan
On 5/7/07, Stefan Becker <[EMAIL PROTECTED]> wrote:
I might be told off by some better SQL-User here on the list -
still here is my 2 Cents worth
> I needed ROW_NUMBER() in PostGresql and I did find the 'temporary
sequence'
> method as a workaround and i think it at least gets the job done
relatively
you use:
> CREATE TEMP SEQUENCE rownum;
> SELECT nextval('rownum') AS row_number , t.a, t.b, t.c
> FROM (
> SELECT a, b, c
> FROM table
> WHERE table_id = 973
> ORDER BY record_date
> ) t;
Doesn't this just return the 973th single record for the current
sequence order in your table?
I believe:
SELECT a, b, c FROM table
offset 973 limit 1
will accomplish the same result.
Stefan
Re: [SQL] PL/PGSQL Record type question
Hi Gabriel, There are two ways to do this: 1. Imad's way (Define the function with the return type as RECORD). Its only problem is that while querying from this function, you need to give a proper SELECT query or else PG returns an error. e.g. As Imad gives in his example ... CREATE FUNCTION xyz() RETURNS record AS $$ declare abc RECORD; begin abc := (1, 2); return abc; end; $$ language plpgsql; And execute the function in this fashion: select a, b from xyz() as (a int, b int); The only problem with this is that if you have 6 elements your select statement becomes quite long. Also, in case your function return parameter count changes, or its types change, you would need to change the SELECT SQL at all the places. 2. Define a TYPE as John mentioned, and set the function's return type as this TYPE. The advantage is that you can always redefine the function and the type in case the return parameters are changing and that your select statement is a simple SELECT * from fn(). Personally, I have tried both and believe the second way (TYPE) is quite convenient for me. Regards, Robins Tharakan On 5/11/07, Gábriel Ákos <[EMAIL PROTECTED]> wrote: Hi, How should I define a record type (there is no table with this record type) programmatically in pl/pgsql? I'd like to return a record with 3 string elements, 2 integers and 1 date. Rgds, Akos -- Üdvözlettel, Gábriel Ákos -=E-Mail :[EMAIL PROTECTED]|Web: http://www.i-logic.hu =- -=Tel/fax:+3612367353|Mobil:+36209278894 =- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Robins
Re: [SQL] GROUP and ORDER BY
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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] GROUP and ORDER BY
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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
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
Re: [SQL] how to concatenate in PostgreSQL
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
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:* [email protected] *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
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.
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
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
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: [email protected] 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! <http://pushthebutton2006.spaces.live.com/>
Re: [SQL] Multiple postgresql functions in a single transaction
> 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: [email protected] > > > 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
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
> > 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: [email protected] > > > 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: [email protected] > 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
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
> > 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: [email protected] > > 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
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.
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 ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] Add a ROWCOUNT to the output of a select.
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 ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Re: [SQL] Substract queries
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: [email protected] 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
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
> 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)"
