By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,852 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,852 IT Pros & Developers. It's quick & easy.

how to edit a function from psql?

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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

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

Nov 23 '05 #2

P: n/a
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 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #3

P: n/a
Thanks Alvaro. That was what I was looking for.
Patrick Hatcher


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

To
"Thomas F.O'Connell" <tf*@sitening.com>
cc
Patrick Hatcher <PH******@macys.com>, pg***********@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.

Nov 23 '05 #4

P: n/a
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.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #5

P: n/a
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 ma*******@postgresql.org

Nov 23 '05 #6

P: n/a
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
<al******@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 ma*******@postgresql.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

Nov 23 '05 #7

P: n/a
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+...

\<.

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

http://archives.postgresql.org

Nov 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.