[SQL]how to select * from database1 table,database2 table

[guard]

if join database1 database2

how to make

I use VB or DELPHI

thanks

[SQL] C/C++ interface

[Volker Paul]

Hello,

in the C interface documentation there is an example using:

res = PQexec(conn, "DECLARE mycursor CURSOR FOR select * from
pg_database");
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "DECLARE CURSOR command failed\n");
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
res = PQexec(conn, "FETCH ALL in mycursor");

...etc. So the statements are:

DECLARE mycursor CURSOR FOR select * from pg_database;
FETCH ALL in mycursor;

What's the difference between this and simply doing:
select * from pg_database;

I tried this in psql, the result seemed the same.

What I'm really using, however, is the C++ interface.
Its documentation is not yet complete.
There, too, I tried a version with and without cursor.
The result seems to be the same, but returned int is always 0
for the version without cursor, so I get no information whether
the query succeeded. 

Is someone maintaining the C++ interface and its documentation?


Thanks,

Volker Paul

[SQL] Inserting BLOB

[Somashekharayya V H]

Hi all,
I am using following query to create new type 'lo'
create type lo
(internallength=4,externallength=10,input=int4in,output=int4out,
default='',passedbyvalue);

i am  trying to insert the image by following query:
insert into table (user_id,image) values
('som',lo_import('/u/grms/Import/XYZ.jpg'));

Here lo_import is used for oid but above type is  'lo' .
It is saying to cast the expression .

Tried by following query also.
insert into table (user_id,image) values ('som','XYZ.jpg');

But it is throwing pg_attoi error : can't parse ' XYZ.jpg'

Could anybody reply earliest with proper queries to insert, update and
delete an image from database.(Postgresql 7.0.0)
(with the help of lo type only)


thanks in advance,
som

Re: [SQL] ORDER BY in SQL functions

[K. Ari Krupnikov]

Tom Lane wrote:
 
 "K. Ari Krupnikov" [EMAIL PROTECTED] writes:
  CREATE FUNCTION foo (INT)
  RETURNS SETOF INT AS '
  SELECT id
  FROM   table
  WHERE  some_colunm  $1
  ORDER BY some_other_colunm
  ' LANGUAGE 'sql';
 
  ERROR:  function declared to return int4 returns multiple values in
  final retrieve
 
 This is a bug in the SQL-function support --- the check for correct
 return type gets confused by the extra hidden column used for the
 ORDER BY.  It'll work if you ORDER BY the column you're returning,
 not that that helps you much.

What if the function is declared to retun a complex type, can I then
sort it?
I it works, this function can be wrapped in another function that simply
returns this single column.

Also, why did I get this message after posting to
comp.databases.postgresql.sql? Do articles in that group automatically
get posted to the mailing list?

 Subject: Stalled post to pgsql-sql
 
 Your message to pgsql-sql has been delayed
 pending approval of the list owner for
 the following reason(s):
 
 Non-Member Submission from "K. Ari Krupnikov" [EMAIL PROTECTED]


-- 
K. Ari Krupnikov

DBDOM - bridging XML and relational databases
http://www.iter.co.il

Re: [SQL] C/C++ interface

[Tom Lane]

Volker Paul [EMAIL PROTECTED] writes:
 Is someone maintaining the C++ interface and its documentation?

Not really.  Feel free to step up and lend a hand ...

regards, tom lane

Re: [SQL] C/C++ interface

[Bruce Momjian]

Cursors and standard queries are pretty much the same, except the cursor
can control the rows returned.

 Hello,
 
 in the C interface documentation there is an example using:
 
 res = PQexec(conn, "DECLARE mycursor CURSOR FOR select * from
 pg_database");
 if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
 {
 fprintf(stderr, "DECLARE CURSOR command failed\n");
 PQclear(res);
 exit_nicely(conn);
 }
 PQclear(res);
 res = PQexec(conn, "FETCH ALL in mycursor");
 
 ...etc. So the statements are:
 
 DECLARE mycursor CURSOR FOR select * from pg_database;
 FETCH ALL in mycursor;
 
 What's the difference between this and simply doing:
 select * from pg_database;
 
 I tried this in psql, the result seemed the same.
 
 What I'm really using, however, is the C++ interface.
 Its documentation is not yet complete.
 There, too, I tried a version with and without cursor.
 The result seems to be the same, but returned int is always 0
 for the version without cursor, so I get no information whether
 the query succeeded. 
 
 Is someone maintaining the C++ interface and its documentation?
 
 
 Thanks,
 
 Volker Paul
 


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

[SQL] COPY isn't working right for me

[Jeff S.]

I have a tab delimited file that I'm trying to import
into an empty table.

I've set the table up as follows:

create table member (
member_id serial not null,
fname varchar(25) not null,
lname varchar(25) not null,
member_since date not null,
Primary Key (member_id)
);

My member.txt file looks like this:

Joe Smith   2000/01/14
Frank   Jones   2000/06/21
MikeDavis   2000/09/24

Here's the copy command I use:

COPY member FROM '/tmp/member.txt';

But I'm getting the following error:

ERROR:  copy: line 1, pg_atoi: error in "Joe": can't
parse "Joe"

I'm assuming this has to do with the member_id with
type serial.  How do I import into this without having
to add the OID's to each of the rows in the text file?



__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35 
a year!  http://personal.mail.yahoo.com/

Re: [SQL] COPY isn't working right for me

[Peter Eisentraut]

Jeff S. writes:

 create table member (
 member_id serial not null,
 fname varchar(25) not null,
 lname varchar(25) not null,
 member_since date not null,
 Primary Key (member_id)
 );

 My member.txt file looks like this:

 Joe   Smith   2000/01/14
 Frank Jones   2000/06/21
 Mike  Davis   2000/09/24

 Here's the copy command I use:

 COPY member FROM '/tmp/member.txt';

 But I'm getting the following error:
 ERROR:  copy: line 1, pg_atoi: error in "Joe": can't
 parse "Joe"

 I'm assuming this has to do with the member_id with
 type serial.  How do I import into this without having
 to add the OID's to each of the rows in the text file?

You can't, using COPY.  You'll have to preprocess your file, either into
INSERT statements, are prepend your own id's.

-- 
Peter Eisentraut  [EMAIL PROTECTED]   http://yi.org/peter-e/

[SQL] constraint/restrict

[Olaf Marc Zanger]

hi there,

with two tables i want to make some constraint-restrictions

create table address ( id serial,  country_id int4, );
and
create table country (id serial, ...);

to make sure that now country-row is deleted if there is still a country_id 
in address table.

e.g.

address: 1, 2, ...
country: 2, ...

now country wouldn't be allowed to be deleted. 

how to do that?

thanks fo help

olaf
-- 
soli-con Engineering Zanger, Dipl.-Ing. (FH) Olaf Marc Zanger
Lorrainestrasse 23, 3013 Bern / Switzerland
fon:+41-31-332 9782, mob:+41-76-572 9782
mailto:[EMAIL PROTECTED], http://www.soli-con.com