Re: [GENERAL] Re: PostreSQL SQL for MySQL SQL

[fabrizio . ermini]

On 6 Feb 2001, at 14:24, Chuck Esterbrook wrote:

> At 01:10 PM 2/6/2001 -0600, John Burski wrote:
> >interactively, via psql, via the Perl Pg module, or via PHP.  If you 
> >attempt to drop a database that doesn't exist, PostgreSQL will issue an 
> >error message.  If you're running interactively, you'll see the message; 
> >if you're accessing via a Perl module or PHP, you can check the query 
> >results to see if an error occurred.  I'm fairly certain that this same 
> >mechanism exists if you're using C or C++ to access your databases.
> 
> I'd prefer to skip the error message, because otherwise my regression test 
> suite will barf, saying something like "Test X failed due to SQL error". I 
> suppose I work in some code to catch this and swallow it.
> 
You can always search the system catalog to know if a DB exists 
or not.

This is how I do it using pglib, in C:

 sprintf(Query,"SELECT * from pg_database where 
datname='%s';",DBname);
res = PQexec(conn, Query);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
Throw_Error(121);
NumMax=PQntuples(res);

if(NumMax==1)
{
sprintf(Query,"DROP DATABASE %s;",DBname);
res = PQexec(conn, Query);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
Throw_Error(122);
}

> >I'm not familiar with the "use Foo" functionality of MySQL, so I can't 
> >discuss it.
> 
> I think you may have answered it with your "\connect dbname" comment. 
> Provided that I can put that after the "create database" in the SQL script 
> and feed the whole mess to psql.
> 

Sure you can. If you use psql as command interpreter, "\connect 
dbname" has almost 1:1 functionality in respect to MySql's "use 
foo". If you use a pglib-based API (i.e. you're using C, Perl, PHP or 
other) you got to use the connection function to select the db.

HTH, bye!


/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

Fabrizio Ermini   Alternate E-mail:
C.so Umberto, 7   [EMAIL PROTECTED]
loc. Meleto Valdarno  Mail on GSM: (keep it short!)
52020 Cavriglia (AR)  [EMAIL PROTECTED]

Re: [GENERAL] Re: PostreSQL SQL for MySQL SQL

[Dan Wilson]

: I think it's still a good idea to provided the "if exists" clause in a
: future PostgreSQL version for these reasons:
:  1. it's convenient
:  2. it doesn't interfere with existing functionality or
performance
:  3. it makes porting from MySQL to PostgreSQL easier

I second this!  It should probably be easy functionality to add (although
I'm no C guru and definetly don't know the ins and out of postgres).  I've
made an equivalent in PHP, but it would be much easier if I could use it
within pgsql dump/import scripts. Plus it requires an additional call to the
database.

-Dan

[GENERAL] Re: PostreSQL SQL for MySQL SQL

[Mitch Vincent]

> I'm fairly new to PostreSQL, coming from MySQL. My Python application
> generates these MySQL commands:
>
> drop database if exists Foo;
> create database Foo;
> use Foo;
> Using the PostgreSQL online docs, my closest translation is:
>
> drop database Foo;
> create database Foo;

You supply the database name on connect.. I don't *think* you can change
databases after you've connected but I could be very wrong (if you can, I'd
sure like to know!)

> What I'm missing is:
> * "if exists" for the drop, in order to avoid an error the very first time
> this program runs
> * the "use Foo;"
>
> Can I do these things in PostreSQL from SQL (e.g., not from the command
line)?

Not sure on these...

-Mitch