Обсуждение: Questions about functionality

On 02/04/2005 10:06:49 AM, Ignacio Colmenero wrote:
> Hello.
>
> I have been in this list for a very short period of time so, if my
> questions
> have been answered before, please tell me and I will browse again in
> the
> archives.
>
> 1. Is there anything in Postgre or third-party solutions similar to
> Oracle's
> SQL Loader, to upload a flat file into a table, according to certain
> rules?
> Any solutions you have tried before to solve this issue?

I don't know what you mean by "according to certain rules", but see
the SQL COPY command or the psql \copy command (client apps
section of the documentation).  You can always
pre-process your files with awk or whatnot.  I've done a bunch of
shell scripting to produce INSERT statements and fed them
to psql.

>
> 2. What are the nuts and bolts to run a SQL script from a batch file?
> I have
> browsed the documentation and I haven't seen anything clear.

psql -d database < /path/to/sql-script

It's a Unix thing, although I believe DOS will do this too.

In Unix you could put
#!/usr/bin/psql -d database
as the first line (and first character) of your sql script and then
make the file executable.

> 3. Can I store more than one polygon in a polygon datatype field,
> like
> ((x1,y1),(x2,y2),.),((x10,y10),(x11,y11),.) or I have to install
> PostGIS?

There are array data types, but I have not used them.  I imagine
you could make an array of polygons.

> 4. Can I query an object in another database, like in Oracle's
> dblink?

I'm no expert.  I don't believe so.  You can query across scheams
in the same database but not across databases.  You could do
something (anything!) by writing an external function in C or
whatever, but I couldn't say how much work that would take.


Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

Karl O. Pinc wrote:
>> 4. Can I query an object in another database, like in Oracle's  dblink?
>
>
> I'm no expert.  I don't believe so.  You can query across scheams
> in the same database but not across databases.  You could do
> something (anything!) by writing an external function in C or
> whatever, but I couldn't say how much work that would take.
>
>
> Karl <kop@meme.com>
> Free Software:  "You don't pay back, you pay forward."
>                 -- Robert A. Heinlein
>

Look in /contrib directory for dblink. That will enable you to query other pg databases.

On Fri, 2005-02-04 at 11:59, Karl O. Pinc wrote:
> On 02/04/2005 10:06:49 AM, Ignacio Colmenero wrote:

> > 4. Can I query an object in another database, like in Oracle's
> > dblink?
>
> I'm no expert.  I don't believe so.  You can query across scheams
> in the same database but not across databases.  You could do
> something (anything!) by writing an external function in C or
> whatever, but I couldn't say how much work that would take.

Actually, there's a contrib module that does just that.
contrib/dblink.  It's useful for running autonomous transactions also.

On Fri, Feb 04, 2005 at 09:06:49AM -0700, Ignacio Colmenero wrote:

> 1. Is there anything in Postgre or third-party solutions similar to Oracle's
> SQL Loader, to upload a flat file into a table, according to certain rules?
> Any solutions you have tried before to solve this issue?

PostgreSQL (or Postgres, but not "Postgre") has a COPY command:

http://www.postgresql.org/docs/8.0/static/sql-copy.html

> 2. What are the nuts and bolts to run a SQL script from a batch file? I have
> browsed the documentation and I haven't seen anything clear.

When you say "batch file" are you referring to Windows batch files?
I can't help with Windows issues, but presumably the psql command
on that platform works the same way as on Unix, where you can use
"psql -f filename" or redirect the file to psql's standard input:

psql -f foo.sql
psql < foo.sql

If you're using a language that supports "here documents" then you
could do something like this:

psql <<END_OF_SQL
SELECT version();
SELECT current_timestamp;
END_OF_SQL

See the psql documentation for more information:

http://www.postgresql.org/docs/8.0/static/app-psql.html

> 3. Can I store more than one polygon in a polygon datatype field, like
> ((x1,y1),(x2,y2),.),((x10,y10),(x11,y11),.) or I have to install PostGIS?

If it fits your needs, you could define a column to be an array of
polygons:

CREATE TABLE foo (poly polygon[]);
INSERT INTO foo VALUES ('{"((1,1),(2,2),(3,3))","((4,4),(5,5),(6,6))"}');

SELECT * FROM foo;
                     poly
-----------------------------------------------
 {"((1,1),(2,2),(3,3))","((4,4),(5,5),(6,6))"}

SELECT poly[1] FROM foo;
        poly
---------------------
 ((1,1),(2,2),(3,3))
(1 row)

See the documentation for "Arrays" and "Array Functions and Operators":

http://www.postgresql.org/docs/8.0/static/arrays.html
http://www.postgresql.org/docs/8.0/static/functions-array.html

> 4. Can I query an object in another database, like in Oracle's dblink?

You can query other PostgreSQL databases with the contrib/dblink
module.  You can also write set-returning functions in procedural
languages like PL/Perl, PL/Tcl, and PL/Python, so you can exploit
any database-querying capabilities those languages provide.  David
Fetter's DBI-Link module does this, for example.  I've written my
own functions to query other databases and wrapped them in views,
so a query like "SELECT * FROM viewname" might actually pull data
from Oracle, MySQL, MSSQL, etc.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Fri, Feb 04, 2005 at 09:06:49AM -0700, Ignacio Colmenero wrote:
> Hello.
>
> I have been in this list for a very short period of time so, if my
> questions have been answered before, please tell me and I will
> browse again in the archives.
>
> 1. Is there anything in Postgre or third-party solutions similar to
> Oracle's SQL Loader, to upload a flat file into a table, according
> to certain rules?  Any solutions you have tried before to solve this
> issue?

COPY is not exactly like SQL*Loader, in the sense that you can't tell
it things like "It's OK for 0.1% of these to be bad."  Such a feature
would be a Very Nice Thing(TM), but hasn't been implemented yet.

Please feel free to help spec and/or write the thing :)

> 2. What are the nuts and bolts to run a SQL script from a batch
> file? I have browsed the documentation and I haven't seen anything
> clear.
>
> 3. Can I store more than one polygon in a polygon datatype field,
> like ((x1,y1),(x2,y2),.),((x10,y10),(x11,y11),.) or I have to
> install PostGIS?

Others have answered these.

> 4. Can I query an object in another database, like in Oracle's dblink?

Yes.  contrib/dblink lets you query other PostgreSQL databases
efficiently.  If you need to query other kinds of data sources,
including Oracle, you can use DBI-Link
http://pgfoundry.org/projects/dbi-link/

> As you may guess, Oracle Databases have been my bread and butter for
> the last 5 years, but now I am dealing with Postgre, and I think it
> is great!!!!

I know you didn't know this in advance, but it's PostgreSQL or
Postgres or PG.  "Postgre" raises hackles the way "'Frisco" does for
people who live in the City By The Bay.

> Sorry if my questions are kind of newbie.

We are all n00b at some level.  Welcome to the community :)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

The world rejoiced as ignacio.colmenero@micotan.com ("Ignacio Colmenero") wrote:
[Large amount of HTML removed...  _Please_ post in plain text...]

There is no direct analogue to Oracle's SQL*Loader product for
PostgreSQL at this time.

That being said...

1.  Plenty of scripting languages are available for Unix platforms,
and they are plenty good at "massaging" data.

2.  I recall Jan Wieck having created a Tcl-based "load tool"
prototype about a year ago that had capabilities somewhat resembling
SQL*Loader.  (Very "somewhat" :-).)  It sort of disappeared, which is
a bit of a shame.  It might be possible to convince him to put that up
on GBorg or PGFoundry.  It's a tool someone else should take on at
this point in time; the ideas, at least, could help in building such a
tool.

3.  Pervasive Software seems to have some Windows-based tools they are
releasing for doing ETL.  That might be useful.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://linuxdatabases.info/~cbbrowne/languages.html
But  what can  you  do with  it?   -- ubiquitous  cry from  Linux-user
partner.  -- Andy Pearce, <ajp@hpopd.pwd.hp.com>

Just a small note to thank David Fetter, Michael Fuhr, Bricklen Anderson,
Scott Marlowe, Karl O. Pinc, and Christopher Browne for their support and
for helping me to understand a little bit more about PostgreSQL (I won't say
Postgre anymore).

Ignacio.

-----------------------------------------------
Ignacio Colmenero
Software Development
Micotan Software Company Ltd.