pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedassorted Postgres SQL/ORDBMS questions
3+ messages / 3 participants
[nested] [flat]
* assorted Postgres SQL/ORDBMS questions
@ 2003-10-21 01:40 Clint Stotesbery <cstotes@hotmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Clint Stotesbery @ 2003-10-21 01:40 UTC (permalink / raw)
To: pgsql-sql
1. You can raise exceptions but you can't catch exceptions in pgsql right?
2. Does Postgres support ORDBMS operations?
Specifically I am wondering about the ability to define your own objects and
create functions/procedures for the objects (e.g. object.method()). In
Oracle I would use CREATE TYPE and CREATE TYPE BODY. Postgres' create type
seems quite different than Oracle's version and they don't seem equivalent
to each other. The Postgres version seems like it is for creating your own
datatypes but not your own objets. I couldn't find any docs on this except
on the SQL commands page.
3. Does it support nested tables?
Again I couldn't find any info in the docs for this.
4. Can dates only be storied in YYYY-MM-DD format?
I've looked over the documentation at
http://www.postgresql.org/docs/7.3/static/functions-formatting.html and it
seems that doing to_date(t_date,''DD-MON-YYYY'') should return 20-OCT-2003
but it returns 2003-10-20 no matter what I do.
An example:
CREATE OR REPLACE FUNCTION datetest()
RETURNS date AS '
DECLARE
t_date varchar;
v_date date;
BEGIN
t_date := to_char(now(),''DD-MON-YYYY'');
v_date := to_date(t_date,''DD-MON-YYYY'');
RETURN v_date;
END;
' LANGUAGE 'plpgsql';
SELECT datetest();
this returns:
datetest
----------
2003-10-20
I wanted it to return 20-OCT-2003 and the documentation suggests that I
should be able to do that yet it doesn't actually do it.
Now slightly different:
CREATE OR REPLACE FUNCTION datetest()
RETURNS varchar AS '
DECLARE
t_date varchar;
v_date date;
BEGIN
t_date := to_char(now(),''DD-MON-YYYY'');
v_date := to_date(t_date,''DD-MON-YYYY'');
RETURN t_date;
END;
' LANGUAGE 'plpgsql';
SELECT datetest();
This returns:
----------
datetest
20-OCT-2003
This works fine but it is a varchar. I really want it to be stored like that
but in a date type instead.
Thanks for the answers!
-Clint
_________________________________________________________________
Add MSN 8 Internet Software to your current Internet access and enjoy
patented spam control and more. Get two months FREE!
http://join.msn.com/?page=dept/byoa
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: assorted Postgres SQL/ORDBMS questions
@ 2003-10-21 02:47 Josh Berkus <josh@agliodbs.com>
parent: Clint Stotesbery <cstotes@hotmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Josh Berkus @ 2003-10-21 02:47 UTC (permalink / raw)
To: Clint Stotesbery <cstotes@hotmail.com>; pgsql-sql
Clint,
> 1. You can raise exceptions but you can't catch exceptions in pgsql right?
right. We'd like to do exception-trapping, but nobody has offered to program
it.
> 2. Does Postgres support ORDBMS operations?
> Specifically I am wondering about the ability to define your own objects
> and create functions/procedures for the objects (e.g. object.method()). In
> Oracle I would use CREATE TYPE and CREATE TYPE BODY. Postgres' create type
> seems quite different than Oracle's version and they don't seem equivalent
> to each other. The Postgres version seems like it is for creating your own
> datatypes but not your own objets. I couldn't find any docs on this except
> on the SQL commands page.
Not with that syntax, no. Our ORDBMS functionality is more aimed at creating
your own datatypes, domains, operators, aggregates, and similar.
> 3. Does it support nested tables?
> Again I couldn't find any info in the docs for this.
No. Nor will it if I have any clout on the Hackers list; Nested tables are a
Bad Idea and they Violate The Relational Standard. Arrays are as far as I am
willing to go, and only in special cases.
> 4. Can dates only be storied in YYYY-MM-DD format?
Dates are stored in an internal format in order to ensure compliance with the
SQL date standard. The DATE type is stored as an integer; the TIMESTAMP is
(I believe) binary. Depending on your locale, the default *representation*
of dates may be yyyy-mm-dd, or something else.
> I've looked over the documentation at
> http://www.postgresql.org/docs/7.3/static/functions-formatting.html and it
> seems that doing to_date(t_date,''DD-MON-YYYY'') should return 20-OCT-2003
> but it returns 2003-10-20 no matter what I do.
You want to re-format the date; see the docs on to_char().
> This works fine but it is a varchar. I really want it to be stored like
> that but in a date type instead.
No, you don't need it to be stored that way.
If you want dates to display a particular way, use to_char() when you query
them. Dates are stored as dates, not as strings.
BTW, MS SQL Server's implementation of DATETIME sucks rocks and violates the
SQL standard besides. So don't go comparing them on me.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: assorted Postgres SQL/ORDBMS questions
@ 2003-10-21 03:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2003-10-21 03:59 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Clint Stotesbery <cstotes@hotmail.com>; pgsql-sql
Josh Berkus <josh@agliodbs.com> writes:
>> 4. Can dates only be storied in YYYY-MM-DD format?
> Dates are stored in an internal format in order to ensure compliance with the
> SQL date standard. The DATE type is stored as an integer; the TIMESTAMP is
> (I believe) binary.
Just to clarify: dates are stored as an integer number of days before or
after some "day zero" (which is probably 1/1/1970 or 1/1/2000, but I
forget at the moment). Timestamps are stored as a possibly fractional
number of seconds before or after the timestamp origin, which I do
recall is midnight 1/1/2000. These representations are compact to store
and are eminently suitable for datetime arithmetic. They have nothing
whatever to do with the input or output string representation; there is
a ton of code in there to get from the one to the other.
> Depending on your locale, the default *representation*
> of dates may be yyyy-mm-dd, or something else.
See the DATESTYLE parameter setting for some discussion of your options
here. Also, to_date, to_timestamp, and to_char are available for
special-purpose format conversions when no existing datestyle makes you
happy.
I quite concur with Josh that there is no percentage in storing dates or
times as strings. Use the provided datatypes --- there's a huge amount
of useful infrastructure in there.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-10-21 03:59 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-21 01:40 assorted Postgres SQL/ORDBMS questions Clint Stotesbery <cstotes@hotmail.com>
2003-10-21 02:47 ` Josh Berkus <josh@agliodbs.com>
2003-10-21 03:59 ` Tom Lane <tgl@sss.pgh.pa.us>
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