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:

Thanks Alvaro. That was what I was looking for.

Patrick Hatcher

Alvaro Herrera <alvherre@dcc.uchile.cl>
11/04/04 03:40 PM

To
"Thomas F.O'Connell" <tfo@sitening.com>
cc
Patrick Hatcher <PHatcher@macys.com>, pgsql-general@postgresql.org
Subject
Re: [GENERAL] how to edit a function from psql?

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 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 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.

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+...

\<.

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

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:

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.

\<.