[SQL] where-used function
Hello All: Hope everyone is enjoying a peaceful holiday season. I am using this quite time between the holidays to get my dev and production environment back into sync. It would be very helpful, if I had a where-used function. This function, given an object name, would determine if the object exists in the database ( i.e. is it a table or a view) and then, most important, what other objects are dependant on the object. A database I worked with years ago, distributed by a marketing company in the NorthWest USA, had such a function. As I remember that function was buggy. Does anybody know if a where-used function exists for the wonderful database known as Postgres? Many Thanks Kevin Duffy
[SQL] Object create date
Hi list, I'm having a hard time trying to find out if the latest patches have been applied to my application (uses lots of pgplsql functions). Does Postgres store creation date and/or modification date for tables, functions and other objects? It would help me a lot if I could query each object when it was created. Is this information available on 8.3? Where should I look? Thanks, Fernando
Re: [SQL] Object create date
On Mon, Dec 29, 2008 at 11:23 AM, Fernando Hevia wrote: > Hi list, > > I'm having a hard time trying to find out if the latest patches have been > applied to my application (uses lots of pgplsql functions). > Does Postgres store creation date and/or modification date for tables, > functions and other objects? > It would help me a lot if I could query each object when it was created. Is > this information available on 8.3? Where should I look? PostreSQL doesn't track this kind of thing for you. An easy method to implement yourself is to create a table to track such changes, and add a line to insert data into that table. create table change_track (version numeric(12,2) primary key, title text, summary text); Then in a script, always update like so: begin; insert into change_track(10.2, 'plpgsql - add / remove','New plpgsql stored procedure to add and remove users. adduser(uid,''username''), deluser(uid)'); create function commit; That way, if some part of the update fails it all fails and you don't have any of it in your db. Then you can just check change_track to see what stuff is in your db. Plus you can check the scripts into svn for management. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Object create date
Fernando Hevia escribió: Hi, > I'm having a hard time trying to find out if the latest patches have > been applied to my application (uses lots of pgplsql functions). > Does Postgres store creation date and/or modification date for tables, > functions and other objects? No. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] where-used function
"Kevin Duffy" writes: > It would be very helpful, if I had a where-used function. This > function, given an object name, would determine > if the object exists in the database ( i.e. is it a table or a view) > and then, most important, what other objects are dependant on the > object. The traditional manual way to do the latter is begin; drop ; ... read the error message about what the drop would cascade to ... rollback; If you feel like getting down-and-dirty with the implementation you could troll the contents of pg_depend for yourself. I think it would be kind of painful to do without writing some C code though, because a lot of useful operations like getObjectDescription() aren't exposed at the SQL level. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Object create date
1. not exactly what you were looking for, but i answer this partially by putting a commented-out CVS expansion tag (e.g. $Id:) in the body of the function so that it gets into the catalog and can be searched: CREATE OR REPLACE FUNCTION foo () RETURNS void AS $BODY$ -- $Id: foo.sql,v 1.6 2008/12/23 00:06:52 gpavlov Exp $ BEGIN ... and query it by something like this: select routine_name, substring(routine_definition from E'%#\042-- #\044Id: % Exp #\044#\042%' for '#') as cvs_id from information_schema.routines ; 2. you can also make some inference about the relative timing of object creation based on the OIDs (query pg_catalog.pg_proc rather than information_schema.routines for proc OIDs). > -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of Fernando Hevia > Sent: Monday, December 29, 2008 10:23 AM > To: pgsql-sql@postgresql.org > Subject: [SQL] Object create date > > Hi list, > > I'm having a hard time trying to find out if the latest patches have > been applied to my application (uses lots of pgplsql functions). > Does Postgres store creation date and/or modification date for tables, > functions and other objects? > It would help me a lot if I could query each object when it was > created. Is this information available on 8.3? Where should I look? > > Thanks, > Fernando > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Object create date
> -Mensaje original- > De: pgsql-sql-ow...@postgresql.org > [mailto:pgsql-sql-ow...@postgresql.org] En nombre de Scott Marlowe > > On Mon, Dec 29, 2008 at 11:23 AM, Fernando Hevia > wrote: > > Hi list, > > > > I'm having a hard time trying to find out if the latest > patches have > > been applied to my application (uses lots of pgplsql functions). > > Does Postgres store creation date and/or modification date > for tables, > > functions and other objects? > > It would help me a lot if I could query each object when it was > > created. Is this information available on 8.3? Where should I look? > > PostreSQL doesn't track this kind of thing for you. Too bad it doesn't. I think it would be quite useful that the database saved the creation time of at least some objects. > An easy method to implement yourself is to create a table to track > such changes, and add a line to insert data into that table. > > create table change_track (version numeric(12,2) primary key, > title text, summary text); > > Then in a script, always update like so: > > begin; > insert into change_track(10.2, 'plpgsql - add / remove','New > plpgsql stored procedure to add and remove users. > adduser(uid,''username''), deluser(uid)'); > > create function > > commit; > Although it's not a solution for an already messed-up database it is an interesting solution to consider for the future. Thanks Scott. Regards, Fernando. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Object create date
Thanks Pavlov for your response. > -Mensaje original- > De: George Pavlov [mailto:gpav...@mynewplace.com] > wrote: > > Hi list, > > > > I'm having a hard time trying to find out if the latest > patches have > > been applied to my application (uses lots of pgplsql functions). > > Does Postgres store creation date and/or modification date > for tables, > > functions and other objects? > > It would help me a lot if I could query each object when it was > > created. Is this information available on 8.3? Where should I look? > > 1. not exactly what you were looking for, but i answer this > partially by putting a commented-out CVS expansion tag (e.g. > $Id:) in the body of the function so that it gets into the > catalog and can be searched: > > CREATE OR REPLACE FUNCTION foo () > RETURNS void AS > $BODY$ > -- $Id: foo.sql,v 1.6 2008/12/23 00:06:52 gpavlov Exp $ > BEGIN > ... > I am already doing this. Sadly I've found it to be very fragile in face of a careless programmer who forgets to update the tags. Myself being the prime suspect. :) > and query it by something like this: > > select > routine_name, > substring(routine_definition from E'%#\042-- #\044Id: % > Exp #\044#\042%' for '#') as cvs_id > from information_schema.routines > ; This query is very helpful. > > 2. you can also make some inference about the relative timing > of object creation based on the OIDs (query > pg_catalog.pg_proc rather than information_schema.routines > for proc OIDs). > I am not sure this would be helpful since different databases are involved (same product on several installations). I think that with the above query I will be able to sort things out. Thank you. Regards, Fernando. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Object create date
Fernando Hevia escribió: > Thanks Pavlov for your response. > > CREATE OR REPLACE FUNCTION foo () > > RETURNS void AS > > $BODY$ > > -- $Id: foo.sql,v 1.6 2008/12/23 00:06:52 gpavlov Exp $ > > BEGIN > > ... > > > > I am already doing this. Sadly I've found it to be very fragile in > face of a careless programmer who forgets to update the tags. Myself > being the prime suspect. :) You don't update the tags. They are updated automatically by CVS (or Subversion, whatever you use) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Copy question
Hello every body I'm trying to extract a query as follows: copy (select * from clientes where id_cliente = 7895) to '/home/paso/sel.copy'; But it returns the next error: ERROR: error de sintaxis en o cerca de «(» at character 6 LINE 1: copy (select * from clientes where id_cliente = 7895) to '/h... I was reading the manual and I think the sintaxis is correct, any idea wich is the cause of the error? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Copy question
Judith Altamirano a écrit : > Hello every body I'm trying to extract a query as follows: > > copy (select * from clientes where id_cliente = 7895) to > '/home/paso/sel.copy'; > > But it returns the next error: > > ERROR: error de sintaxis en o cerca de «(» at character 6 > LINE 1: copy (select * from clientes where id_cliente = 7895) to '/h... > > > I was reading the manual and I think the sintaxis is correct, any idea > wich is the cause of the error? > It depends on your PostgreSQL release. It should work with 8.2 and later. It won't with earlier releases. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
