[SQL] demo of using large objects interface in php
Hi,
Finally, I can give the demo of using large objects interface in php.
the test table:
create table lo_test (
id serial,
raster oid,
primary key(id)
);
file: lo_insert.php
\n";
$handle = pg_loopen ($db, $oid, "w");
echo ("handle=$handle\n");
$fp = fopen ("/etc/rc.d/rc.local", "r");
echo "$fp";
while (!feof($fp)) {
pg_lowrite($handle, fread($fp, 4096));
}
fclose($fp);
pg_loclose($handle);
pg_exec($db, "insert into lo_test (raster) values ($oid)");
pg_exec($db, "commit");
pg_close($db);
?>
file: lo_read.php
\n";
$handle = pg_loopen ($db, $oid, "r");
echo ("$handle\n");
//pg_loreadall($handle);
while ($buf=pg_loread($handle, 80)) {
echo nl2br($buf);
}
pg_loclose($handle);
pg_exec($db, "commit");
pg_close($db);
?>
file: lo_delete.php
\n";
pg_lounlink ($oid);
pg_exec($db, sprintf('delete from lo_test where id = %s', $record[0]));
pg_exec($db, "commit");
pg_close($db);
?>
There is a better way to delete the large object associated with the record.
create trigger like this:
CREATE FUNCTION lo_test_del () RETURNS OPAQUE AS '
BEGIN
perform lo_unlink(OLD.raster);
RETURN OLD;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER "tg_lo_test_del" BEFORE DELETE ON "lo_test" FOR EACH ROW EXECUTE
PROCEDURE "lo_test_del" ();
when you run
'delete from lo_test where id = 1'
the large object will be deleted in cascade.
regards
Danny Chen
[EMAIL PROTECTED]
[SQL] how to r/w blob field in php
hi, everyone I decide to use text field to store large text but pgsql has 8k limit in insert statement. I have read the sample of create large object of pg_sql in the php manual. here it is: Example 1. Using Large Objects 1 2 13 I am confused. According this sample, but which table and which record will be updated ? Anyone has tried this ? Please give me a full sample thanks
[SQL] generic return for functions
Hi, this is my first time using mailing list. Someone over at tek-tips
suggested that I try here to see if someone can help me.
I am currently converting everything from SQL Server to PostgreSQL. This is
for an application that is going to support Oracle, SQL Server and
PostgreSQL at the same time. I have done a lot of the conversion already
but I am stuck on functions that returns parts of views or tables.
In SQL Server, you can create User Defined functions that returns type
"TABLE"... so then you can simply return the result of a select statement...
(e.g. return select * from mytable)
The problem is that I don't know if there is a way to do this in PostgreSQL.
My functions and stored procedures in SQL Server involves select statement
that gets columns from few views and tables.
I know I can create my own data type with all the columns that are going to
be returned, or get my function to return a type "record"... however, I
don't like both of these methods since some of my functions involves
returning a select statement using inner join and all that stuff... some
have like 30 columns
There seems to be another way to use refcursor but my application is in
ColdFusion... refcursor doesn't seem to work with it.
I know "returns setof record" and "returns setof my_own_datatype" work, but
I would like to know if there is a better way? Something that's like
"returns setof record" but without having to define all the columns when I
call the function? {i.e. without the need to do: select * from myfunction()
as (column1 type1, column2...);}
If there is such method? It will allow me to maintain the application much
easier and makes the conversion task much easier :)
Thanks.
/Danny
_
Tired of spam? Get advanced junk mail protection with MSN 8.
http://join.msn.com/?page=features/junkmail
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [postgres] Antwort von Microsoft auf Mail hier!
Ja, sone Mail habe ich auch mal bekommen.. da ist wohl was zwischen MS und Yahoo im Busch ... Gruß, Danny Am Mon, 13 Oct 2003 14:18:41 +0200 hat Alvar Freude <[EMAIL PROTECTED]> geschrieben: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Hallo allerseits, > > wer hat denn Zugriff auf die Subscriber-Liste dieser Liste? > > Eben bekam ich auf meine Mail oben eine Antwort von *Microsoft*. Der > Inhalt > unten. > > > Also, entweder ist da jemand offiziell eingetragen, oder hat einen Deal > mit > Yahoogroups, dass dies unsichtbar geschieht. Wäre ja eine Möglichkeit, um > potentielle Konkurrenz zu beobachten: alles was "postgres" im Namen hat > geht an "[EMAIL PROTECTED]" oder so ... > > > Oder jemand hat scherzeshalber MS hier eingetragen. > > > Sehr ominös. > > > > Ciao > Alvar > > > - -- Forwarded Message -- > Date: Montag, Oktober 13, 2003 04:11:51 -0700 > From: [EMAIL PROTECTED] > To: [EMAIL PROTECTED] > Subject: RE: RE: [SQL] [postgres] Foreign Key - Remove > > MSDN Auto-Response: Please do not reply back to this e-mail as this is > not > a monitored mailbox. > > We really do want your submission. To enable us to provide you with a > more > timely response, please submit your questions or feedback via the MSDN > Contact Us web form at > http://register.microsoft.com/contactus30/contactus.asp?domain=msdn > Please > select an option from the list that best aligns with the subject of your > mail. > > **Please note: If you have a Microsoft Product or technical (how to) > support question, you > will get a faster resolution and be better served by using the options > available and detailed in the Microsoft Support Site at: > http://support.microsoft.com Links to the knowledge base, support phone > numbers, online support and > submitting feedback about our products options are available from within > the left hand side navigation. > _ Q&A: > Q1: Why have you switched from e-mail to a Web form? A1: The MSDN Contact > Us Web form enables us to provide you with a quicker > response by eliminating the huge volumes of spam that our e-mail > addresses > attract. We recognize that legitimate customers like yourself have used > this e-mail address in the past to contact Microsoft. This auto reply is > sent to inform you of these changes and your options. Please bookmark the > MSDN Contact Us link above and remove the MSDN mail-to e-mail address you > used from your address book. > > Q2: When did Microsoft.com Contact Us implement this new process? A2: > April 4th 2003. > > Q3: Why did I get this auto-response if I am responding to an existing > MSDN > Contact Us inquiry that has this return e-mail address? A3: Take a look > at your sent messages folder in your e-mail application to > review the subject line of the e-mail you sent this alias. Does it have > an > MSDN Contact Us inquiry number in the format of CSTx x xID, where x is an > eight-digit number? - - Yes: Unfortunately we can only process inquiry > less than three months > old. Please create a new inquiry using the MSDN Contact Us link above. - - > > > No: If you removed or edited the subject line of the e-mail, please > resend your e-mail to this address with the original subject line. It > should have the original Microsoft.com Contact Us inquiry number. > > Q4: How do I know that someone will actually read my feedback? A4: Our > MSDN Contact Us customer support representatives read every piece > of feedback that we receive via the MSDN Contact Us Web form. If you ask > us > a question and provide us with your e-mail address, we will send you a > response. If you send us feedback, we will route it to the right people > at > Microsoft. > > Q5: Why did your Web site have a link to this e-mail address? A5: We > replaced e-mail links on our Web site with links to the MSDN Contact > Us Web form. If you found one that we missed, please visit the MSDN > Contact > Us Web form (link above) and let us know. We really appreciate your > assistance. > > - -- End Forwarded Message -- > > > > > > - -- ** Alvar C.H. Freude -- http://alvar.a-blast.org/ > ** ** ODEM.org-Tour: http://tour.odem.org/ > ** > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.2.3 (FreeBSD) > > iD8DBQE/ipghOndlH63J86wRApSKAKCL5VPsmxmXI4sy9VCPtq4Xjstk+ACgyP7u > DkE5quj0AJ1HMJu6xtSRLss= > =rNQ5 > -END PGP SIGNATURE- > > > Yahoo! Groups Sponsor > > Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie > eine E-Mail an: >
[SQL] [postgres] Copy Timestamp NULL
Hi Leute, Ich habe vor einiger Zeit eine spezielle Frage gestellt, die nicht ganz beantwortet wurde. Also es geht darum, dass ich in eine Postgres Tabelle per Copy Daten laden möchte. Bei einer Spalte handelt es sich um Timestamp NULL ... In der Source-Datei befinden sich in dieser Spalte ISO Timestamp Werte und NULL - Werte . (Nicht jede Zeile besitzt einen Timestamp-wert) Die Source-Datei sieht prinzipiell so aus : 1|Hallo|17.0|1999-01-23 14:30:08.456234|usr01 2|Test|18.5||usr02 Die Spalte 4 ist somit vom Typ Timestamp. Allerdings kann ich diese Source-Datei nicht in die Postgres Tabelle per Copy laden, da es an den NULL - Stellen zum Fehler kommt : Bad timestamp external representation '' Wenn also eine "Lücke" gefunden wird, interpretiert Copy diese "Lücke" nicht als Null-Wert und beschwert sich über das fehlerhafte Timestamp Format. Es geht ebenfalls nicht, wenn ich an der entsprechenden Stelle NULL schreibe. Was muss ich also angeben, damit ich solch eine Datei laden kann ? Viele Grüße, Danny -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/ Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 8: explain analyze is your friend
