select using date

How can I see a view in psql after having created it? If I enter
\d <viewname> I can see the fields, but how can I see what makes up the
view? Best if the original CREATE statement could be listed somehow.

Kaare Rasmussen wrote:

How can I see a view in psql after having created it? If I enter
\d <viewname> I can see the fields, but how can I see what makes up the
view? Best if the original CREATE statement could be listed somehow.

We could use EXPLAIN VIEW for this...
New item for TODO?

EXPLAIN TABLE, EXPLAIN INDEX, etc

Vadim

Kaare Rasmussen wrote:

How can I see a view in psql after having created it? If I enter
\d <viewname> I can see the fields, but how can I see what makes up the
view? Best if the original CREATE statement could be listed somehow.

We could use EXPLAIN VIEW for this...
New item for TODO?

EXPLAIN TABLE, EXPLAIN INDEX, etc

How about:

SELECT * from pg_views
WHERE viewname = 'fred';

OR

SELECT pg_get_viewdef('fred');

I have added this to the TODO list:

* have psql \d on a view show the view definition.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Kaare Rasmussen wrote:

How can I see a view in psql after having created it? If I enter
\d <viewname> I can see the fields, but how can I see what makes up the
view? Best if the original CREATE statement could be listed somehow.

We could use EXPLAIN VIEW for this...
New item for TODO?

EXPLAIN TABLE, EXPLAIN INDEX, etc

Vadim

SELECT * FROM pg_views;

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

Try:

select current_date, CURRENT_DATE - INTERVAL '1 DAY';
?column?|?column?
----------+----------------------
1999-01-08|1999-01-07 00:00:00+01
(1 row)

PostgreSQL has a syntax sligth different than SQL92. You have to enclose
'1 DAY' instead of '1' DAY.

-Jose'-

Kevin Heflin wrote:

On Fri, 8 Jan 1999, Jose' Soares wrote:

Try:

select current_date, CURRENT_DATE - INTERVAL '1 DAY';
?column?|?column?
----------+----------------------
1999-01-08|1999-01-07 00:00:00+01
(1 row)

PostgreSQL has a syntax sligth different than SQL92. You have to enclose
'1 DAY' instead of '1' DAY.

Well, I tried:

select current_date, CURRENT_DATE - INTERVAL '1 DAY';

but receive the following:

ERROR: There is no operator '-' for types 'date' and 'timespan'
You will either have to retype this query using an explicit cast,
or you will have to define the operator using CREATE OPERATOR

After trying somethings, I was able to get this to work:

select CURRENT_DATE -1;

will subtract 1 day from the current date... which gives me what I need
for now.

Thanks

Kevin

--------------------------------------------------------------------
Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103
VP/Mac Tech | 333 Texas St #619 | FAX:318.221.6612
kheflin@shreve.net | Shreveport, LA 71101 | http://www.shreve.net
--------------------------------------------------------------------