Thread: how to edit a function from psql?

To the best of my knowledge, this is not possible in psql.

You can edit individual queries with \e, but I don't think it's
possible to edit functions.

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Nov 4, 2004, at 11:11 AM, Patrick Hatcher wrote:

> How can I view and edit a function in psql?  I have been using PgAdmin
> to do this but wanted to try the command line
> TIA
>
>  Patrick Hatcher

On Thu, Nov 04, 2004 at 02:22:06PM -0600, Thomas F.O'Connell wrote:
> To the best of my knowledge, this is not possible in psql.
>
> You can edit individual queries with \e, but I don't think it's
> possible to edit functions.

It is of course possible get the definition using \df+ and then use
CREATE OR REPLACE in conjuntion with \e to edit it at will.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Tulio: oh, para qué servirá este boton, Juan Carlos?
Policarpo: No, aléjense, no toquen la consola!
Juan Carlos: Lo apretaré una y otra vez.

Alvaro,

How do you get the results of \df+ into the buffer with \e? Just copy
and paste?

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Nov 4, 2004, at 3:40 PM, Alvaro Herrera wrote:

> On Thu, Nov 04, 2004 at 02:22:06PM -0600, Thomas F.O'Connell wrote:
>> To the best of my knowledge, this is not possible in psql.
>>
>> You can edit individual queries with \e, but I don't think it's
>> possible to edit functions.
>
> It is of course possible get the definition using \df+ and then use
> CREATE OR REPLACE in conjuntion with \e to edit it at will.
>
> --
> Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
> Tulio: oh, para qué servirá este boton, Juan Carlos?
> Policarpo: No, aléjense, no toquen la consola!
> Juan Carlos: Lo apretaré una y otra vez.

On Thu, Nov 04, 2004 at 04:00:10PM -0600, Thomas F. O'Connell wrote:

Thomas,

> How do you get the results of \df+ into the buffer with \e? Just copy
> and paste?

Right.  Single quotes tended to be an issue.  Not so with 8.0.  It's
much better, of course, to have the original definition on a text file
somewhere ...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Este mail se entrega garantizadamente 100% libre de sarcasmo.

What would be really useful would be a command that would return the
actual SQL needed to create a function. At present the output from
\df+ needs to be pieced together to create a new CREATE OR REPLACE
string. A command that returnes that string for you would allow you to
copy and paste it in, do a minor edit and run the command very
quickly.

I've had to tweak functions quite a lot lately and having to construct
the command from the output from \df+ can be a bit of a pain.

In fact, it would be useful to have this for all entity types in the
system: tables, views and types etc. Perhaps a new symbol to follow
the \d command, such as * (just a random guess).

So:

\df* functionname

would output the CREATE OR REPLACE line for that funtion.

Thoughts?

On Thu, 4 Nov 2004 19:44:53 -0300, Alvaro Herrera
<alvherre@dcc.uchile.cl> wrote:
> On Thu, Nov 04, 2004 at 04:00:10PM -0600, Thomas F. O'Connell wrote:
>
> Thomas,
>
> > How do you get the results of \df+ into the buffer with \e? Just copy
> > and paste?
>
> Right.  Single quotes tended to be an issue.  Not so with 8.0.  It's
> much better, of course, to have the original definition on a text file
> somewhere ...
>
> --
> Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
> Este mail se entrega garantizadamente 100% libre de sarcasmo.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>


--

Russ

Here is what I get:

orfs=# \df+ get_datasets
                                                                                                List of functions
 Result data type |    Schema    |     Name     |               Argument
data types               | Owner | Language
|                              Source
code                                                                        | Description

------------------+--------------+--------------+-------------------------------------------------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
 refcursor        | weather_data | get_datasets | refcursor, character
varying, character varying | kan4  | plpgsql  |
DECLARE
        _person_ ALIAS FOR $2;
        _where_  ALIAS FOR $3;
        selectstring text;

BEGIN
        selectstring := get_datasets_selstr(_person_, _where_);
        -- RAISE NOTICE '%', selectstring;
        OPEN $1 FOR EXECUTE selectstring;
        RETURN $1;
END;
 |
(1 row)

orfs=# \e
CREATE FUNCTION

When I issue \e, the editor window pops up, apparently with the contents
of the query buffer. When I exit the editor, the function definition is
applied to create this function (the one in the buffer).

orfs=# \?
<snip>
Query Buffer
  \e [FILE]      edit the query buffer (or file) with external editor
  \g [FILE]      send query buffer to server (and results to file or
|pipe)
  \p             show the contents of the query buffer
  \r             reset (clear) the query buffer
  \s [FILE]      display history or save it to file
  \w [FILE]      write query buffer to file
<snip>

How did that function definition get in the query buffer? Seems that it
sure would be nice to fill it \df+...

\<.




On Thu, 2004-11-04 at 14:40, Alvaro Herrera wrote:
> On Thu, Nov 04, 2004 at 02:22:06PM -0600, Thomas F.O'Connell wrote:
> > To the best of my knowledge, this is not possible in psql.
> >
> > You can edit individual queries with \e, but I don't think it's
> > possible to edit functions.
>
> It is of course possible get the definition using \df+ and then use
> CREATE OR REPLACE in conjuntion with \e to edit it at will.

What version of postgres are you using? In postgresql-7.4.6, I get an
empty query buffer when I try what you describe here.

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Nov 4, 2004, at 5:23 PM, Karim Nassar wrote:

> Here is what I get:
>
> orfs=# \df+ get_datasets
>
>                          List of functions
>  Result data type |    Schema    |     Name     |
> Argument
> data types               | Owner | Language
> |                              Source
> code
>      | Description
> ------------------+--------------+--------------
> +-------------------------------------------------+-------+----------
> +----------------------------------------------------------------------
> -----------------------------------------------------------------------
> -----------------------------------------------------------------------
> -----------------------------------------------------------------------
> ------------------------------------+-------------
>  refcursor        | weather_data | get_datasets | refcursor, character
> varying, character varying | kan4  | plpgsql  |
> DECLARE
>         _person_ ALIAS FOR $2;
>         _where_  ALIAS FOR $3;
>         selectstring text;
>
> BEGIN
>         selectstring := get_datasets_selstr(_person_, _where_);
>         -- RAISE NOTICE '%', selectstring;
>         OPEN $1 FOR EXECUTE selectstring;
>         RETURN $1;
> END;
>  |
> (1 row)
>
> orfs=# \e
> CREATE FUNCTION
>
> When I issue \e, the editor window pops up, apparently with the
> contents
> of the query buffer. When I exit the editor, the function definition is
> applied to create this function (the one in the buffer).
>
> orfs=# \?
> <snip>
> Query Buffer
>   \e [FILE]      edit the query buffer (or file) with external editor
>   \g [FILE]      send query buffer to server (and results to file or
> |pipe)
>   \p             show the contents of the query buffer
>   \r             reset (clear) the query buffer
>   \s [FILE]      display history or save it to file
>   \w [FILE]      write query buffer to file
> <snip>
>
> How did that function definition get in the query buffer? Seems that it
> sure would be nice to fill it \df+...
>
> \<.

On Sun, 2004-11-07 at 20:36, Thomas F.O'Connell wrote:
> What version of postgres are you using? In postgresql-7.4.6, I get an
> empty query buffer when I try what you describe here.

7.4.5

I got it. Apparently the query buffer is the last run query (good or
bad). So, to get my function into the buffer, I would have to cut and
paste it (which I do often, and is what my last post showed). Backslash
functions are not included. I assume you ran no queries before \e, and
got an empty buffer.

Seems like \e is merely a convenient way to deal with long queries. I
always have my editor open, so this isn't much of a win for me.

Still, it might be nice to have a way to get the function into the
buffer without cutting and pasting from the (normally open) editor.

As a point of interest...

1)  orfs=# select * from person
2)  orfs-# ;
3)  ERROR:  relation "person" does not exist
4)  orfs=# \e
5)  orfs-# select
6)  orfs-# *
7)  orfs-# from
8)  orfs-# person
9)  orfs-# ;
10) ERROR:  syntax error at or near "select" at character 23
11) orfs=# \e
12) orfs=# select * from person;
13) ERROR:  relation "person" does not exist
14) orfs=# \e


At line 4, the query buffer has:
~~~~~~~~~~~~~~~~~~~~~
select * from person
~~~~~~~~~~~~~~~~~~~~~

Note that there is no semi-colon.

At line 11, the contents of the query buffer is:
~~~~~~~~~~~~~~~~~~~~~
select * from person

select
*
from
person
~~~~~~~~~~~~~~~~~~~~~~

And at line 14:
~~~~~~~~~~~~~~~~~~~~~~
select * from person;
~~~~~~~~~~~~~~~~~~~~~~


It seems that the query editor just dumps the contents to the command
line. Why the semi-colons from lines 2 and 9 aren't in the query buffers
is a mystery to me.

\<.