pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMINUS & ROWNUM in PostGres
8+ messages / 4 participants
[nested] [flat]
* MINUS & ROWNUM in PostGres
@ 2003-09-01 13:47 Jomon Skariah <jomons@aztec.soft.net>
2003-09-04 19:50 ` Re: MINUS & ROWNUM in PostGres Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 8+ messages in thread
From: Jomon Skariah @ 2003-09-01 13:47 UTC (permalink / raw)
To: pgsql-sql
Hi,
We are in the process of migrating of our application from Oracle to
PostGreSQL.
we are facing a few problems with PL/SQL Code..
1) Do we have a replacement in PostGres for MINUS operator of Oracle .
2 Also I need to find an alternative for ROWNUM in oracle..
Can anyone help ..
Regards
Jomon
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: MINUS & ROWNUM in PostGres
2003-09-01 13:47 MINUS & ROWNUM in PostGres Jomon Skariah <jomons@aztec.soft.net>
@ 2003-09-04 19:50 ` Rod Taylor <rbt@rbt.ca>
2003-09-08 08:21 ` Re: MINUS & ROWNUM in PostGres Kris Jurka <books@ejurka.com>
0 siblings, 1 reply; 8+ messages in thread
From: Rod Taylor @ 2003-09-04 19:50 UTC (permalink / raw)
To: Jomon Skariah <jomons@aztec.soft.net>; +Cc: pgsql-sql
> 1) Do we have a replacement in PostGres for MINUS operator of Oracle .
I believe MINUS is non-standard word for EXCEPT, correct?
> 2 Also I need to find an alternative for ROWNUM in oracle..
If you are looking for a unique identifier, try using the OID.
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: MINUS & ROWNUM in PostGres
2003-09-01 13:47 MINUS & ROWNUM in PostGres Jomon Skariah <jomons@aztec.soft.net>
2003-09-04 19:50 ` Re: MINUS & ROWNUM in PostGres Rod Taylor <rbt@rbt.ca>
@ 2003-09-08 08:21 ` Kris Jurka <books@ejurka.com>
0 siblings, 0 replies; 8+ messages in thread
From: Kris Jurka @ 2003-09-08 08:21 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: Jomon Skariah <jomons@aztec.soft.net>; pgsql-sql
On Thu, 4 Sep 2003, Rod Taylor wrote:
> > 2 Also I need to find an alternative for ROWNUM in oracle..
>
> If you are looking for a unique identifier, try using the OID.
>
ROWID is oracle's unique identifier, ROWNUM is the row number in a query
result. In the past it has been suggested that ROWNUM could be replaced
by the nextval of a temporary sequence.
Kris Jurka
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: MINUS & ROWNUM in PostGres
@ 2003-09-08 13:44 Jomon Skariah <jomons@aztec.soft.net>
2003-09-08 14:11 ` Re: MINUS & ROWNUM in PostGres Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 8+ messages in thread
From: Jomon Skariah @ 2003-09-08 13:44 UTC (permalink / raw)
To: Kris Jurka <books@ejurka.com>; Rod Taylor <rbt@rbt.ca>; +Cc: pgsql-sql
Hi,
Do we have any replacement for REPLACE() of Oracle in PostGres?
I know we can function to do same job.
Regards
Jomon
-----Original Message-----
From: Kris Jurka [mailto:books@ejurka.com]
Sent: Monday, September 08, 2003 1:52 PM
To: Rod Taylor
Cc: Jomon Skariah; pgsql-sql@postgresql.org
Subject: Re: [SQL] MINUS & ROWNUM in PostGres
On Thu, 4 Sep 2003, Rod Taylor wrote:
> > 2 Also I need to find an alternative for ROWNUM in oracle..
>
> If you are looking for a unique identifier, try using the OID.
>
ROWID is oracle's unique identifier, ROWNUM is the row number in a query
result. In the past it has been suggested that ROWNUM could be replaced
by the nextval of a temporary sequence.
Kris Jurka
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: MINUS & ROWNUM in PostGres
2003-09-08 13:44 Re: MINUS & ROWNUM in PostGres Jomon Skariah <jomons@aztec.soft.net>
@ 2003-09-08 14:11 ` Rod Taylor <rbt@rbt.ca>
0 siblings, 0 replies; 8+ messages in thread
From: Rod Taylor @ 2003-09-08 14:11 UTC (permalink / raw)
To: Jomon Skariah <jomons@aztec.soft.net>; +Cc: Kris Jurka <books@ejurka.com>; pgsql-sql
On Mon, 2003-09-08 at 09:44, Jomon Skariah wrote:
> Hi,
>
> Do we have any replacement for REPLACE() of Oracle in PostGres?
What does replace() do? String replacement?
http://www.postgresql.org/docs/7.3/interactive/functions-string.html
replace(string text, from text, to text)
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: MINUS & ROWNUM in PostGres
@ 2003-09-09 04:14 Jomon Skariah <jomons@aztec.soft.net>
2003-09-09 12:19 ` Re: MINUS & ROWNUM in PostGres Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 8+ messages in thread
From: Jomon Skariah @ 2003-09-09 04:14 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: Kris Jurka <books@ejurka.com>; pgsql-sql
Hi,
Thanx for your reply.
The problem is we are using PostGres 7.1.In this version REPLACE() is not
available.
Regards
Jomon
-----Original Message-----
From: Rod Taylor [mailto:rbt@rbt.ca]
Sent: Monday, September 08, 2003 7:41 PM
To: Jomon Skariah
Cc: Kris Jurka; pgsql-sql@postgresql.org
Subject: RE: [SQL] MINUS & ROWNUM in PostGres
On Mon, 2003-09-08 at 09:44, Jomon Skariah wrote:
> Hi,
>
> Do we have any replacement for REPLACE() of Oracle in PostGres?
What does replace() do? String replacement?
http://www.postgresql.org/docs/7.3/interactive/functions-string.html
replace(string text, from text, to text)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: MINUS & ROWNUM in PostGres
2003-09-09 04:14 Re: MINUS & ROWNUM in PostGres Jomon Skariah <jomons@aztec.soft.net>
@ 2003-09-09 12:19 ` Rod Taylor <rbt@rbt.ca>
2003-09-10 12:57 ` Re: MINUS & ROWNUM in PostGres A.Bhuvaneswaran <bhuvan@symonds.net>
0 siblings, 1 reply; 8+ messages in thread
From: Rod Taylor @ 2003-09-09 12:19 UTC (permalink / raw)
To: Jomon Skariah <jomons@aztec.soft.net>; +Cc: Kris Jurka <books@ejurka.com>; pgsql-sql
> The problem is we are using PostGres 7.1.In this version REPLACE() is not
> available.
It sounded like you were just starting to work on the change over. I
highly suggest upgrading to 7.3 at the very least, 7.4 if you're going
to be a few months prior to going to production. A ton of good work has
been put into PostgreSQL in the last couple of years.
Anyway, you'll need to write your own replace() if you stick with that
release.
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: MINUS & ROWNUM in PostGres
2003-09-09 04:14 Re: MINUS & ROWNUM in PostGres Jomon Skariah <jomons@aztec.soft.net>
2003-09-09 12:19 ` Re: MINUS & ROWNUM in PostGres Rod Taylor <rbt@rbt.ca>
@ 2003-09-10 12:57 ` A.Bhuvaneswaran <bhuvan@symonds.net>
0 siblings, 0 replies; 8+ messages in thread
From: A.Bhuvaneswaran @ 2003-09-10 12:57 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: Jomon Skariah <jomons@aztec.soft.net>; Kris Jurka <books@ejurka.com>; pgsql-sql
> Anyway, you'll need to write your own replace() if you stick with that
> release.
>
You can use this replace function, if you stick with that release.
regards,
bhuvaneswaran
Attachments:
replace.sql (text/plain, 1.0K) download
create or replace function replace (varchar, varchar, varchar) returns varchar as '
declare
string alias for $1;
find alias for $2;
replacement alias for $3;
match integer;
end_before integer;
start_after integer;
string_replaced varchar;
string_remainder varchar;
begin
string_remainder := string;
string_replaced := '''';
match := position(find in string_remainder);
while (match > 0) loop
end_before := match - 1;
start_after := match + length(find);
string_replaced := string_replaced || substr(string_remainder, 1, end_before) || replacement;
string_remainder := substr(string_remainder, start_after);
match := position(find in string_remainder);
raise notice ''replaced %, remainder %'', string_replaced, string_remainder;
end loop;
string_replaced := string_replaced || string_remainder;
return string_replaced;
end;
' LANGUAGE 'plpgsql';
-- rmello@fslc.usu.edu
end of thread, other threads:[~2003-09-10 12:57 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-01 13:47 MINUS & ROWNUM in PostGres Jomon Skariah <jomons@aztec.soft.net>
2003-09-04 19:50 ` Rod Taylor <rbt@rbt.ca>
2003-09-08 08:21 ` Kris Jurka <books@ejurka.com>
2003-09-08 13:44 Re: MINUS & ROWNUM in PostGres Jomon Skariah <jomons@aztec.soft.net>
2003-09-08 14:11 ` Rod Taylor <rbt@rbt.ca>
2003-09-09 04:14 Re: MINUS & ROWNUM in PostGres Jomon Skariah <jomons@aztec.soft.net>
2003-09-09 12:19 ` Rod Taylor <rbt@rbt.ca>
2003-09-10 12:57 ` A.Bhuvaneswaran <bhuvan@symonds.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox