how to edit a function from psql?

#1

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

#2

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:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#3

Thanks Alvaro. That was what I was looking for.

Patrick Hatcher

Alvaro Herrera <alvherre (AT) dcc (DOT) uchile.cl>
11/04/04 03:40 PM

To
"Thomas F.O'Connell" <tfo (AT) sitening (DOT) com>
cc
Patrick Hatcher <PHatcher (AT) macys (DOT) com>, pgsql-general (AT) postgresql (DOT) 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:

Quote:

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.

#4

On Thu, Nov 04, 2004 at 02:22:06PM -0600, Thomas F.O'Connell wrote:

Quote:

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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#5

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:

Quote:

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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#6

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

Thomas,

Quote:

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 (AT) postgresql (DOT) org

#7

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 (AT) dcc (DOT) uchile.cl> wrote:

Quote:

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 (AT) postgresql (DOT) org

--

Russ

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#8

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:

Quote:

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

\<.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

how to edit a function from psql?

comp.databases.postgresql.general comp.databases.postgresql.general

how to edit a function from psql? - 11-04-2004 , 11:11 AM

Re: how to edit a function from psql? - 11-04-2004 , 02:22 PM

Re: how to edit a function from psql? - 11-04-2004 , 03:32 PM

Re: how to edit a function from psql? - 11-04-2004 , 03:40 PM

Re: how to edit a function from psql? - 11-04-2004 , 04:00 PM

Re: how to edit a function from psql? - 11-04-2004 , 04:44 PM

Re: how to edit a function from psql? - 11-05-2004 , 04:22 AM

Re: how to edit a function from psql? - 11-07-2004 , 09:36 PM