Thread: How to out \df to a file

Craig wrote:
> Hello All,
>
>
>
> I am trying to get a list of the functions built into postgreSQL.
>
> I know to use the \df command w/n psql, but paging through that is painful.
>
> Is there a way to send the \df output to a file?
>
> Or is there a table/view (s) I could query?

psql -E will show you the query;  that is in the FAQ.

    $ psql -E test
    psql (8.4devel)
    Type "help" for help.

    test=> \df
    ********* QUERY **********
    SELECT n.nspname as "Schema",
      p.proname as "Name",
      pg_catalog.pg_get_function_result(p.oid) as "Result data type",
      pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types"
    FROM pg_catalog.pg_proc p
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
    WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
          AND p.proargtypes[0] IS DISTINCT FROM
    'pg_catalog.cstring'::pg_catalog.regtype
          AND NOT p.proisagg
          AND n.nspname <> 'pg_catalog'
      AND pg_catalog.pg_function_is_visible(p.oid)
    ORDER BY 1, 2, 4;
    **************************

                       List of functions
     Schema | Name | Result data type | Argument data types
    --------+------+------------------+---------------------
    (0 rows)

That is for Postgres 8.4.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Bruce,

Thanks for your quick reply!  Between your response and George's I was able
to get exactly what I needed.
That "-E" is a wonderful option.  I have already put it into my snippets
file!

Grace & Peace.

Craig

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Bruce Momjian
Sent: Wednesday, April 01, 2009 9:22 AM
To: Craig
Cc: pgsql-novice@postgresql.org
Subject: [personal] Re: [NOVICE] How to out \df to a file

Craig wrote:
> Hello All,
>
>
>
> I am trying to get a list of the functions built into postgreSQL.
>
> I know to use the \df command w/n psql, but paging through that is
painful.
>
> Is there a way to send the \df output to a file?
>
> Or is there a table/view (s) I could query?

psql -E will show you the query;  that is in the FAQ.

    $ psql -E test
    psql (8.4devel)
    Type "help" for help.

    test=> \df
    ********* QUERY **********
    SELECT n.nspname as "Schema",
      p.proname as "Name",
      pg_catalog.pg_get_function_result(p.oid) as "Result data type",
      pg_catalog.pg_get_function_arguments(p.oid) as "Argument data
types"
    FROM pg_catalog.pg_proc p
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
    WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
          AND p.proargtypes[0] IS DISTINCT FROM
    'pg_catalog.cstring'::pg_catalog.regtype
          AND NOT p.proisagg
          AND n.nspname <> 'pg_catalog'
      AND pg_catalog.pg_function_is_visible(p.oid)
    ORDER BY 1, 2, 4;
    **************************

                       List of functions
     Schema | Name | Result data type | Argument data types
    --------+------+------------------+---------------------
    (0 rows)

That is for Postgres 8.4.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.0.238 / Virus Database: 270.11.35/2033 - Release Date: 04/01/09
06:06:00

Craig wrote:
> Bruce,
>
> Thanks for your quick reply!  Between your response and George's I was able
> to get exactly what I needed.
> That "-E" is a wonderful option.  I have already put it into my snippets
> file!

I think the \o idea is best if you want something that will be
consistent from Postgres release to Postgres release because we
internally adjust the \df query occasionally to match changes in the
system tables.

Also, in 8.4, you might need \dfS because we are going to display only
user-created objects by default, unless 'S' or a pattern is supplied.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

I'm writing a photo database for my family photos.  I've got a massive
back log so I'd like to some large scale annotation and break things up
into manageable chunks.

I think one useful assumption would be tha any set of photos taken within
of a few seconds of each other (say 5 or less) are /probably/ about the
same subject...

How would I go about detecting and marking events where the time interval
between two photos is less than 5 seconds

photo_id  | photo_time          | photo_group
         1 | 2005-08-20 19:05:15 |
         2 | 2005-08-20 19:12:59 |
         3 | 2005-08-20 19:13:03 |
         4 | 2005-08-21 19:13:10 |
         6 | 2005-08-21 13:02:48 |
         7 | 2005-08-21 13:04:58 |
         8 | 2005-08-21 13:06:06 |
         9 | 2005-08-21 13:06:10 |
        10 | 2005-08-21 13:09:19 |

would become something like

photo_id  | photo_time          | photo_group
         1 | 2005-08-20 19:05:15 |
         2 | 2005-08-20 19:12:59 |  1
         3 | 2005-08-20 19:13:03 |  1
         4 | 2005-08-21 19:13:07 |  1
         6 | 2005-08-21 13:02:48 |
         7 | 2005-08-21 13:04:58 |
         8 | 2005-08-21 13:06:06 |  2
         9 | 2005-08-21 13:06:10 |  2
        10 | 2005-08-21 13:06:19 |

My first thought was to reach for perl :-) but I was wondering if there
was an 'easy' way to do it in postgresSQL ...

--
Michael
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Michael John Lush PhD            Tel:44-1223 492626
Bioinformatician
HUGO Gene Nomenclature Committee    Email: hgnc@genenames.org
European Bioinformatics Institute
Hinxton, Cambridge
URL: http://www.genenames.org
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Wed, 1 Apr 2009, Michael Lush wrote:

>
> I'm writing a photo database for my family photos.  I've got a massive back
> log so I'd like to some large scale annotation and break things up into
> manageable chunks.
>
> I think one useful assumption would be tha any set of photos taken within of
> a few seconds of each other (say 5 or less) are /probably/ about the same
> subject...
>
> How would I go about detecting and marking events where the time interval
> between two photos is less than 5 seconds
>
[snip]

Have a look at the OVERLAPS operator.


--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 512-248-2683                 E-Mail: ler@lerctr.org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893

On Wed, 1 Apr 2009, Larry Rosenman wrote:
> On Wed, 1 Apr 2009, Michael Lush wrote:
>> I'm writing a photo database for my family photos.  I've got a massive back
>> log so I'd like to some large scale annotation and break things up into
>> manageable chunks.
>>
>> I think one useful assumption would be tha any set of photos taken within
>> of a few seconds of each other (say 5 or less) are /probably/ about the
>> same subject...
>>
>> How would I go about detecting and marking events where the time interval
>> between two photos is less than 5 seconds
>>
> [snip]
>
> Have a look at the OVERLAPS operator.

Thanks!  That gets me to pairs of dates/IDs,  where should I look to find
out how to extract and number the clusters of IDs?

photo_id  | photo_time          | photo_group
         1 | 2005-08-20 19:05:15 |
         2 | 2005-08-20 19:12:59 |  1
         3 | 2005-08-20 19:13:03 |  1
         4 | 2005-08-21 19:13:07 |  1
         6 | 2005-08-21 13:02:48 |
         8 | 2005-08-21 13:06:06 |  2
         9 | 2005-08-21 13:06:10 |  2
        10 | 2005-08-21 13:06:19 |

ie if 2 and 3 are about the same thing and 3 and 4 are about the same
thing, 2 and 4 are likely to be about the same thing, even though they
were taken more than 5 seconds apart.

--
Michael
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Michael John Lush PhD                   Tel:44-1223 492626
Bioinformatician
HUGO Gene Nomenclature Committee        Email: hgnc@genenames.org
European Bioinformatics Institute
Hinxton, Cambridge
URL: http://www.genenames.org
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~