[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

[SQL] How to excute dynamically a generated SQL command?

[John Zhang]

Hi the list,

Referring to the PostgreSQL 8.3 documentation " 38.5.4. Executing Dynamic
Commands ", the command for executing a dynamic command is:
EXECUTE command-string [ INTO [STRICT] target ];


I am to execute an sql statement created dynamically, which is represented
in a variable sSql.
Here is an example:
sSql='INSERT INTO hm_raster.hm_airphotos( file_ext, airphoto) VALUES
('.tif',  lo_import( E''C:\\HM\\Data\\Flightmap.tif'');';
EXECUTE sSQL;

It raises the error as:
ERROR:  syntax error at end of input
LINE 1: ...E'C:\\HM\\Data\\Flightmap.tif')
  ^

I would appreciate a lot if you offer your input. Thanks a lot.

John

[SQL] Add column by using SELECT statement

[John Zhang]

Hi all,

I was wondering how I can add a column and populate it by some query.

For example:
TblA (Id, fld1)
TblB(Id, fld1, fld2)

I have a query:
SELECT b.fld2
FROM tblB b
WHERE condition1

what I want to do is add a column in tblA: fld2
and polpulate the newly added field with the query
on tblA.Id=tblB.Id

Any advice? Any input would be much appreciated.

Thanks a lot
John

Re: [SQL] Comparing two tables of different database

[John Zhang]

Hi Nicholas,

The query is across database query.  dblink is needed for that task.

Hope it helps,

John

On Thu, Apr 30, 2009 at 3:07 PM, Edward W. Rouse wrote:

>  Can’t you use this?
>
>
>
> select name from database2.sr_1 where name not in (select name from
> database2.pr_1);
>
>
>
> My test database VM isn’t running so I can’t test it, but I seem to
> remember that that’s how I did it for a few queries of that type. This is
> assuming the 2 databases are running on the same machine, like the way there
> is template0 as the default and you add addition databases to the same
> ‘instance’. If you are talking about 2 different database servers, then I
> have no idea.
>
>
>
> Edward W. Rouse
>
>
>
> *From:* pgsql-sql-ow...@postgresql.org [mailto:
> pgsql-sql-ow...@postgresql.org] *On Behalf Of *Nicholas I
> *Sent:* Thursday, April 30, 2009 6:12 AM
> *To:* Joshua Tolley
> *Cc:* Adam Ruth; Pawel Socha; pgsql-sql@postgresql.org
> *Subject:* Re: [SQL] Comparing two tables of different database
>
>
>
> Hi All,
>
>   For example,
>
> There are two database.   database1 and database 2;
>
>  database1 has a table called pr_1 with the columns, id,name and time.
>  database2 has a table called sr_1 with the_columns id,name and time.
>
>  i would like to find out the differences that is, find the names that
> are not in sr_1 but in pr_1.
>  we can achieve this by the query,
>
>  select name from sr_1 where name not in (select name from pr_1);
> the above query will work in case of two tables in the same database.
>
>
>  But the problem is, these two tables are in different database. i did
> not understand about the dblink.
>
> is there any exaples on dblink. can we do it without using dblink.
>
> -Nicholas I
>
>
>
>