Обсуждение: Getting The Last Entry
Hello,
I need to get de last entry from a table. I try using:
DECLARE variable CURSOR FOR SELECT id FROM table;
FETCH LAST FROM variable;
And works fine, but when i try to use this code in a procedure, appears the following message:
ERROR: syntax error at or near "FETCH"
LINE XX: FETCH LAST FROM raton;
^
********** Error **********
Is there another way to get that last ID?
----
Rodrigo
On 9 September 2010 23:13, Rodrigo Quezada <rotarantino@gmail.com> wrote: > Hello, > I need to get de last entry from a table. I try using: > DECLARE variable CURSOR FOR SELECT id FROM table; > FETCH LAST FROM variable; > And works fine, but when i try to use this code in a procedure, appears the > following message: > ERROR: syntax error at or near "FETCH" > LINE XX: FETCH LAST FROM raton; > ^ > ********** Error ********** > Is there another way to get that last ID? Try: SELECT id FROM table ORDER BY id DESC LIMIT 1; -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
On 10 September 2010 16:31, Francisco Leovey <fleovey@yahoo.com> wrote: > > if by "last" you mean the last row inserted, use > > select *,oid from table order by oid DESC limit 1 > > (asuming you use OID's) (copying in list with your response) -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
Once I suggested to use oid, but some guys here said was a poor design.
On Fri, Sep 10, 2010 at 12:33 PM, Thom Brown <thom@linux.com> wrote:
On 10 September 2010 16:31, Francisco Leovey <fleovey@yahoo.com> wrote:
>
> if by "last" you mean the last row inserted, use
>
> select *,oid from table order by oid DESC limit 1
>
> (asuming you use OID's)
(copying in list with your response)
--Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
It may be poor design to use OID, but have you ever tried to use a SERIAL field instead? That alternative is a really poor choice, many headaches when you try to move data to other tables or recover a backup. There is a temptation to use the value in the SERIAL field but it is totaly useless in most cases. Duplicate values, etc. --- On Fri, 9/10/10, Luiz Eduardo Cantanhede Neri <lecneri@gmail.com> wrote:
|
(Top-posting corrected.)
Thom Brown wrote:
> On 10 September 2010 16:31, Francisco Leovey <fleovey@yahoo.com
> <mailto:fleovey@yahoo.com>> wrote:
> >
> > if by "last" you mean the last row inserted, use
> >
> > select *,oid from table order by oid DESC limit 1
> >
> > (asuming you use OID's)
>
> (copying in list with your response)
Luiz Eduardo Cantanhede Neri wrote:
>> Once I suggested to use oid, but some guys here said was a poor design.
It is, but you don't just throw the baby out with the bathwater. Use the
"ORDER BY ... DESC LIMIT 1" trick with a column that is not bad design, one
that matches your (as yet unrevealed) definition of "last record", as Thom
suggested.
Within themselves tables have no notion of order or "last" vs. "first" other
than the OID. The reason that OID use is bad is its role as an internal,
DB-only mechanism - it is not connected to the data model and therefore
creates risks that real columns do not.
"It's bad design to use OID" can be countered with, "Yeah, I know, I'm using
it anyway" if you elucidate and ameliorate the risks involved, and show why
the better alternatives (use of a data-model-relevant column to order your
data) cannot be used this time.
But your colleagues do have a point - use the better alternatives unless you
really, really, really, really cannot and are able to fully explain and
justify why not.
Don't let good rules ("Avoid use of the OID") become cargo-cult superstitious
dogma.
--
Lew