Re: [SQL] Object create date

[Asko Oja]

Hi

About three years ago we started doing nightly schema dumps of all our
databases and then updating these into CVS. That has proven to be pretty
useful when there is need to determine when some change was done in
database.

regards,
Asko

On Tue, Dec 30, 2008 at 7:33 PM, Erik Jones  wrote:

>
> On Dec 29, 2008, at 12:30 PM, George Pavlov wrote:
>
>  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).
>>
>
> Hmm...  It seems to me that since object creation time, being metadata,
> would be better served being placed in a COMMENT for the object.  That would
> have the added bonus of being able to search in one place (pg_description)
> across all objects of all types for a given creation/modification date.
>
> Erik Jones, Database Administrator
> Engine Yard
> Support, Scalability, Reliability
> 866.518.9273 x 260
> Location: US/Pacific
> IRC: mage2k
>
>
>
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

[SQL] Question on Escape-string

[John Zhang]

Dear all,

I am using pl/pgsql to develop a function to implement some logic to load
BLOB data, like .tif file, to postgres DB. The issue I am facing is the file
name MUST be with double back-slash \\ in order for pgsql to process the
string properly. However, when the string is Escaped in my function, how can
I pass it in to lo_import() function?

Is there any function to double back-slash a string? Or how can we preserve
a string as RAW?

 ISSUE :
-- use E'C:\\tmp\\tst.tif' for the full file name for IN parameter
of load_blob function.
-- however, when the string is escaped it becomes 'C:\tmp\tst.tif'
as expected
-- the file name need be passed in to lo_import() function again
without double \\
-- when it is passed in and escaped , the \ is gone and the filename
becomes meaningless

Any input would be much appreciated!

Thanks a lot
John