[SQL] Doubt :- Image_Insert
Hello friends I have a doubt, Is it possible to insert images into a postgres db. What is its datatype. Is its possible to insert jpeg images. or v have to store the path into the db. Pls reply. its urgent. Sreejith ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Doubt :- Image_Insert
On Mon, 2004-11-22 at 19:11 +0530, sreejith s wrote: > Hello friends I have a doubt, > Is it possible to insert images into a postgres db. What is its > datatype. Is its possible to insert jpeg images. or v have to store > the path into the db. Pls reply. its urgent. Use bytea for a datatype. PostgreSQL can comfortably handle 50MB's worth of data and uncomfortably handle 1GB if you have enough RAM per tuple. You will need to properly escape the data to do the insertion, but the documentation (for DBI, PHP, Java, and PostgreSQL for the others) all clearly explain the steps to be taken. -- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Doubt :- Image_Insert
Hi, On Mon, Nov 22, 2004 at 08:54:20AM -0500, Rod Taylor wrote: > On Mon, 2004-11-22 at 19:11 +0530, sreejith s wrote: > > Hello friends I have a doubt, > > Is it possible to insert images into a postgres db. What is its > > datatype. Is its possible to insert jpeg images. or v have to store > > the path into the db. Pls reply. its urgent. > > Use bytea for a datatype. PostgreSQL can comfortably handle 50MB's worth > of data and uncomfortably handle 1GB if you have enough RAM per tuple. You mean "handle 50MB's worth of data" per tuple or as a whole ? Same question for 1GB. this is not really clear... bye Jerome Alet ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Doubt :- Image_Insert
On Mon, 2004-11-22 at 15:09 +0100, Jerome Alet wrote: > Hi, > > On Mon, Nov 22, 2004 at 08:54:20AM -0500, Rod Taylor wrote: > > On Mon, 2004-11-22 at 19:11 +0530, sreejith s wrote: > > > Hello friends I have a doubt, > > > Is it possible to insert images into a postgres db. What is its > > > datatype. Is its possible to insert jpeg images. or v have to store > > > the path into the db. Pls reply. its urgent. > > > > Use bytea for a datatype. PostgreSQL can comfortably handle 50MB's worth > > of data and uncomfortably handle 1GB if you have enough RAM per tuple. > > You mean "handle 50MB's worth of data" per tuple or as a whole ? Per tuple. There is no practical limit to how much it can handle as a whole. -- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] get sequence value of insert command
> > > create sequence mysequence;
> > >
> > > create table foo(
> > > id integer default nextval('mysequence'),
> > > bla text,
> > > wombat integer,
> > > foobar date,
> > > primary key(id)
> > > );
> > >
> > > insert into foo (wombat) values (88);
> > >
> > > now how do i know the id of my newly inserted element? and
> > > how can this be done in a completely concurrency safe way?
> >
> > CREATE RULE get_pkey_on_insert AS ON INSERT TO foo DO SELECT
> > currval('mysequence') AS id LIMIT 1;
>
> now that one is really great! you should definitly add it to the
> faq. plus an additional explanation why the limit 1 is needed here.
INSERT INTO foo ( ... ) ( SELECT * FROM foo1 );
>
> thanks!
> erik
>
--
Vadim Passynkov
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] Recursive SETOF function
I'm trying to port some TSQL to PLPGSQL. The DB has a table with a recursive foreign key that represents a tree hierarchy. I'm trying to re-create a TSQL function that pulls out all the ancestors of a given node in the hierarchy. I'm rather new to PLSQL and I have several questions. 1. In TSQL, I can assign a scalar to the result of query like so: SET @var1 = (SELECT foo FROM bar WHERE [EMAIL PROTECTED]) How would I do this in PLSQL? 2. In TSQL the "result table" can be inserted into manually. IE: CREATE FUNCTION foo () RETURNS @ttable TABLE( uid INTEGER) AS BEGIN INSERT @ttable VALUES (1) RETURN END Is there a way to manually insert rows into the result table in PLSQL? What follows is my TSQL function if that helps give context. CREATE FUNCTION svp_getparentproviderids (@child_provider INTEGER) RETURNS @provider_ids TABLE ( uid INTEGER ) AS BEGIN DECLARE @cid AS INTEGER IF (SELECT count(*) FROM providers WHERE uid [EMAIL PROTECTED]) > 0 BEGIN SET @cid = @child_provider WHILE @cid IS NOT NULL BEGIN INSERT @provider_ids VALUES (@cid) SET @cid = (SELECT parent_id FROM providers WHERE [EMAIL PROTECTED]) END END RETURN END -- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Missing SELECT INTO ... DEFAULT VALUES in plpgsql for composite t ypes
Hi all Just self-explanatory code below -- var1 with default value. CREATE DOMAIN var1_type AS pg_catalog.text DEFAULT 'udp'::pg_catalog.text CONSTRAINT "var1_const" CHECK ( VALUE IS NOT NULL AND ( VALUE = 'tcp'::pg_catalog.text OR VALUE = 'udp'::pg_catalog.text ) ); -- var2 without default CREATE DOMAIN var2_type AS pg_catalog.int4 CONSTRAINT "var2_const" CHECK ( VALUE IS NOT NULL AND VALUE > 0 ); -- Let's create composite type foo CREATE TABLE foo ( var1 var1_type, var2 var2_type ); -- and let's create constructor for it CREATE OR REPLACE FUNCTION foo ( int4 ) RETURNS foo AS ' DECLARE this foo; BEGIN /* * I dont want hard coded default * value for this.var1 here * but SELECT INTO this DEFAULT VALUES not possible in plpgsql */ -- SELECT INTO this DEFAULT VALUES; this.var2 := $1; RETURN this; END; ' LANGUAGE 'plpgsql' IMMUTABLE STRICT; SELECT * from foo ( 2 ); var1 | var2 --+-- |2 (1 row) but I want var1 | var2 --+-- udp |2 (1 row) Is anybody know any solution for this? -- Vadim Passynkov ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Recursive SETOF function
I'm feeling sausey today, so here is my (untested) attempt to translate your function. It's inline below, and you'll want to look here http://www.postgresql.org/docs/7.4/interactive/plpgsql.html for more information. On Mon, 22 Nov 2004 09:18:13 -0600, Richard Rowell <[EMAIL PROTECTED]> wrote: > I'm trying to port some TSQL to PLPGSQL. The DB has a table with a > recursive foreign key that represents a tree hierarchy. I'm trying to > re-create a TSQL function that pulls out all the ancestors of a given > node in the hierarchy. > > I'm rather new to PLSQL and I have several questions. > > 1. In TSQL, I can assign a scalar to the result of query like so: > SET @var1 = (SELECT foo FROM bar WHERE [EMAIL PROTECTED]) > > How would I do this in PLSQL? > > 2. In TSQL the "result table" can be inserted into manually. IE: > > CREATE FUNCTION foo () RETURNS @ttable TABLE( uid INTEGER) AS BEGIN > INSERT @ttable VALUES (1) > RETURN > END > > Is there a way to manually insert rows into the result table in PLSQL? > > What follows is my TSQL function if that helps give context. > > CREATE FUNCTION svp_getparentproviderids (@child_provider INTEGER) > RETURNS @provider_ids TABLE ( uid INTEGER ) > AS > BEGIN > DECLARE @cid AS INTEGER > IF (SELECT count(*) FROM providers WHERE uid [EMAIL PROTECTED]) > 0 > BEGIN > SET @cid = @child_provider > WHILE @cid IS NOT NULL > BEGIN > INSERT @provider_ids VALUES (@cid) > SET @cid = (SELECT parent_id FROM providers WHERE [EMAIL > PROTECTED]) > END > END > RETURN > END > -- This TYPE will get you a named column... easier to use SRFs with a preexisting type. CREATE TYPE svp_getparentproviderids_uid_type AS ( uid INTEGER ); CREATE FUNCTION svp_getparentproviderids (INTEGER) RETURNS SETOF svp_getparentproviderids_uid_type AS ' DECLARE child_provider ALIAS FOR $1; cid INTEGER; BEGIN SELECT count(*) FROM providers WHERE uid [EMAIL PROTECTED]) > 0 LOOP cid := child_provider IF cid IS NULL THEN EXIT; END IF; RETURN NEXT cid; SELECT INTO cid parent_id FROM providers WHERE [EMAIL PROTECTED]; END LOOP; RETURN END;' LANGUAGE 'plpgsql'; Hope that helps! > -- > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Recursive SETOF function
Forgot one line. See below On Mon, 22 Nov 2004 11:54:30 -0500, Mike Rylander <[EMAIL PROTECTED]> wrote: > I'm feeling sausey today, so here is my (untested) attempt to [snip] > > CREATE FUNCTION svp_getparentproviderids (@child_provider INTEGER) > > RETURNS @provider_ids TABLE ( uid INTEGER ) > > AS > > BEGIN > > DECLARE @cid AS INTEGER > > IF (SELECT count(*) FROM providers WHERE uid [EMAIL PROTECTED]) > 0 > > BEGIN > > SET @cid = @child_provider > > WHILE @cid IS NOT NULL > > BEGIN > > INSERT @provider_ids VALUES (@cid) > > SET @cid = (SELECT parent_id FROM providers WHERE [EMAIL > > PROTECTED]) > > END > > END > > RETURN > > END > > > > -- This TYPE will get you a named column... easier to use SRFs with a > preexisting type. > CREATE TYPE svp_getparentproviderids_uid_type AS ( uid INTEGER ); > > CREATE FUNCTION svp_getparentproviderids (INTEGER) >RETURNS SETOF svp_getparentproviderids_uid_type >AS ' > DECLARE > child_provider ALIAS FOR $1; > cid INTEGER; > BEGIN SELECT * FROM providers WHERE uid [EMAIL PROTECTED]) > 0 IF NOT FOUND RETURN; END IF; > LOOP > cid := child_provider > IF cid IS NULL THEN > EXIT; > END IF; > RETURN NEXT cid; > SELECT INTO cid parent_id FROM providers WHERE [EMAIL PROTECTED]; > END LOOP; > RETURN > END;' LANGUAGE 'plpgsql'; > > Hope that helps! > > > -- > > > > > > ---(end of broadcast)--- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to [EMAIL PROTECTED] so that your > > message can get through to the mailing list cleanly > > > > > -- > Mike Rylander > [EMAIL PROTECTED] > GPLS -- PINES Development > Database Developer > -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Recursive SETOF function
I have been fiddling with what you sent. I have it working mostly, save for I keep getting syntax errors on the "RETURN NEXT cid;" line. If I remove this line then the function works ( but returns nothing of course). Any ideas on why the RETURN NEXT doesn't like the variable as a parameter? sp_demo_505=# CREATE OR REPLACE FUNCTION svp_getparentproviderids (INTEGER) RETURNS SETOF svp_getparentproviderids_uid_type AS ' DECLARE child_provider ALIAS FOR $1; cid INTEGER; BEGIN SELECT INTO cid count(*) FROM providers WHERE uid =child_provider; IF cid = 0 THEN RAISE EXCEPTION ''Inexistent ID --> %'', child_provider; RETURN; END IF; cid := child_provider; LOOP EXIT WHEN cid IS NULL; RETURN NEXT cid; SELECT INTO cid parent_id FROM providers WHERE uid=cid; END LOOP; RETURN; END;' LANGUAGE 'plpgsql'; CREATE FUNCTION sp_demo_505=# select * from svp_getparentproviderids(21112); ERROR: incorrect argument to RETURN NEXT at or near "cid" CONTEXT: compile of PL/pgSQL function "svp_getparentproviderids" near line 13 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [postgres] Re: [SQL] Recursive SETOF function
Sorry about that... try this: CREATE OR REPLACE FUNCTION svp_getparentproviderids (INTEGER) RETURNS SETOF svp_getparentproviderids_uid_type AS ' DECLARE child_provider ALIAS FOR $1; cid svp_getparentproviderids_uid_type%ROWTYPE; tmp_cid INTEGER; BEGIN SELECT INTO tmp_cid count(*) FROM providers WHERE uid =child_provider; IF tmp_cid = 0 THEN RAISE EXCEPTION ''Inexistent ID --> %'', child_provider; RETURN; END IF; cid.uid := child_provider; LOOP EXIT WHEN tmp_cid IS NULL; RETURN NEXT cid; SELECT INTO tmp_cid parent_id FROM providers WHERE uid=cid.uid; END LOOP; RETURN; END;' LANGUAGE 'plpgsql'; On Mon, 22 Nov 2004 12:51:41 -0600, Richard Rowell <[EMAIL PROTECTED]> wrote: > I have been fiddling with what you sent. I have it working mostly, save > for I keep getting syntax errors on the "RETURN NEXT cid;" line. If I > remove this line then the function works ( but returns nothing of > course). Any ideas? > > sp_demo_505=# CREATE OR REPLACE FUNCTION svp_getparentproviderids > (INTEGER) >RETURNS SETOF svp_getparentproviderids_uid_type >AS ' > DECLARE > child_provider ALIAS FOR $1; > cid INTEGER; > BEGIN > SELECT INTO cid count(*) FROM providers WHERE uid =child_provider; > IF cid = 0 THEN > RAISE EXCEPTION ''Inexistent ID --> %'', child_provider; > RETURN; > END IF; > cid := child_provider; > LOOP > EXIT WHEN cid IS NULL; > RETURN NEXT cid; > SELECT INTO cid parent_id FROM providers WHERE uid=cid; > END LOOP; > RETURN; > END;' LANGUAGE 'plpgsql'; > CREATE FUNCTION > sp_demo_505=# select * from svp_getparentproviderids(21112); > ERROR: incorrect argument to RETURN NEXT at or near "cid" > CONTEXT: compile of PL/pgSQL function "svp_getparentproviderids" near > line 13 > > -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [postgres] Re: [SQL] Recursive SETOF function
I had to fiddle a bit more, but I did finally get it to work. Thanks Mike CREATE OR REPLACE FUNCTION svp_getparentproviderids (INTEGER) RETURNS SETOF INTEGER AS ' DECLARE child_provider ALIAS FOR $1; cid INTEGER; BEGIN SELECT INTO cid count(*) FROM providers WHERE uid =child_provider; IF cid = 0 THEN RAISE EXCEPTION ''Inexistent ID --> %'', child_provider; RETURN; END IF; cid := child_provider; LOOP EXIT WHEN cid IS NULL; RAISE WARNING ''LOOP: Adding (%) to results'', cid; RETURN NEXT cid; SELECT INTO cid parent_id FROM providers WHERE uid=cid; END LOOP; RETURN; END;' LANGUAGE 'plpgsql'; On Mon, 2004-11-22 at 14:39 -0500, Mike Rylander wrote: > Sorry about that... try this: > > CREATE OR REPLACE FUNCTION svp_getparentproviderids > (INTEGER) > RETURNS SETOF svp_getparentproviderids_uid_type > AS ' > DECLARE > child_provider ALIAS FOR $1; > cid svp_getparentproviderids_uid_type%ROWTYPE; > tmp_cid INTEGER; > BEGIN >SELECT INTO tmp_cid count(*) FROM providers WHERE uid =child_provider; >IF tmp_cid = 0 THEN >RAISE EXCEPTION ''Inexistent ID --> %'', child_provider; >RETURN; >END IF; >cid.uid := child_provider; >LOOP >EXIT WHEN tmp_cid IS NULL; >RETURN NEXT cid; >SELECT INTO tmp_cid parent_id FROM providers WHERE uid=cid.uid; >END LOOP; >RETURN; > END;' LANGUAGE 'plpgsql'; > > On Mon, 22 Nov 2004 12:51:41 -0600, Richard Rowell > <[EMAIL PROTECTED]> wrote: > > I have been fiddling with what you sent. I have it working mostly, save > > for I keep getting syntax errors on the "RETURN NEXT cid;" line. If I > > remove this line then the function works ( but returns nothing of > > course). Any ideas? > > > > sp_demo_505=# CREATE OR REPLACE FUNCTION svp_getparentproviderids > > (INTEGER) > >RETURNS SETOF svp_getparentproviderids_uid_type > >AS ' > > DECLARE > > child_provider ALIAS FOR $1; > > cid INTEGER; > > BEGIN > > SELECT INTO cid count(*) FROM providers WHERE uid =child_provider; > > IF cid = 0 THEN > > RAISE EXCEPTION ''Inexistent ID --> %'', child_provider; > > RETURN; > > END IF; > > cid := child_provider; > > LOOP > > EXIT WHEN cid IS NULL; > > RETURN NEXT cid; > > SELECT INTO cid parent_id FROM providers WHERE uid=cid; > > END LOOP; > > RETURN; > > END;' LANGUAGE 'plpgsql'; > > CREATE FUNCTION > > sp_demo_505=# select * from svp_getparentproviderids(21112); > > ERROR: incorrect argument to RETURN NEXT at or near "cid" > > CONTEXT: compile of PL/pgSQL function "svp_getparentproviderids" near > > line 13 > > > > > > -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Recursive SETOF function
SELECT INTO cid count(*) FROM providers WHERE uid =child_provider; Hey, hey. Better : SELECT blablah FROM providers WHERE uid = child_provider LIMIT 1; IF NOT FOUND THEN exit with error ELSE do your stuff Why scan more than 1 row when you just need existence ? Or : SELECT INTO cid parent_id FROM providers WHERE uid=cid; WHILE FOUND RETURN NEXT cid; SELECT INTO cid parent_id FROM providers WHERE uid=cid; END; Not sure about the While syntax but you get the idea. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Image Insert Postgresql DB
Hello I have a doubt regarding Image insert into Postgres DB.
Yesterday i posted a query regarding this. and this one is in
continuation.
I am working on Project with Linux Server and Windows Client
environment. And v r developing windows based application.
V have to scan images from scanner attched to the client machine and
these images r to be inserted into postgres db. Data type for Image
field in the DB is OID.
copy of Image file actually resides in the client. V have to insert
this image into the DB.
Query to insert
INSERT INTO Imagetable VALUES (lo_import('C:\\image\\peach.jpg'));
here the path specified is the image path at client. But its doesn't
work. Since it is not
possible set the querys path to a remote machine.
How i can insert the image into the DB at linux machine where the
image file resides in the client machine.
pls reply
Sreejith
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Image Insert Postgresql DB
Dear Shreejith ,
> Data type for Image
> field in the DB is OID.
> copy of Image file actually resides in the client. V have to insert
> this image into the DB.
Firtsly crosscheck the data type is OID it must be either bytea or blob.
>
> Query to insert
>
> INSERT INTO Imagetable VALUES (lo_import('C:\\image\\peach.jpg'));
Out of experience I would suggest you to keep images on the file
system as database with images in it would be bulky and would takes
loads of time to restore and backup.
If these do not bother you continue with it.
Thats Pascal ??
Dont know about pascal but you would have to stream the file to the
server. I doubt that PostgreSQL will fetch from the client.
In PHP we would have Posted the file and exected the insert query
Hope this helps you.
--
With Best Regards,
Vishal Kashyap.
Lead Software Developer,
http://saihertz.com,
http://vishalkashyap.tk
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Image Insert Postgresql DB
On Tuesday 23 November 2004 09:51 am, sreejith s wrote:
> Query to insert
>
> INSERT INTO Imagetable VALUES (lo_import('C:\\image\\peach.jpg'));
have you succeeded in inserting an image into the database from a file on the
server using the above query? AFAIK you need a bytea datatype, and also the
image data has to be properly escaped (\\) before pg will accept it. I assume
you are using a scripting language with a dbapi. Your steps would be:
1. read the image file into a variable
2. escape the image data
3. insert this into the database in a bytea field
if you follow this procedure it is irrelevant as to where the source file is
- client or server
regards
kg
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Image Insert Doubt
Dear Vishal, I have posted a doubt regarding image insert into Postgres. Thanx for ur valuable information. I am new to Postgres/Linux Environment. Intially i thought it would be better to store image path into DB. Later i changed my idea. Since it will be difficult/Tedious to backup both DB and Image folder. From Windows application how i can access a folder in Linux and save my image data there. I have configured a folder via Samba Server. Its asking user name password when i first access the folder. Is it can be avoided. My Second doubt is I want dump DB from my windows client application, 'pg_dump' command with additional parameters not working. but its working from server. how is this possible. Sreejith ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Image Insert Postgresql DB
I face this same problem when I try to insert image by lo_import to
database with my client files. I already change the way to bytea data
type but it's not work. The error return like type mismatch when I try
to insert it from Binary variable.
Why I cannot insert it? If anybody found some solution please reply on
this topic for me too.
Thank You.
>>> Kenneth Gonsalves <[EMAIL PROTECTED]> 23/11/2004 12:18:44 pm
>>>
On Tuesday 23 November 2004 09:51 am, sreejith s wrote:
> Query to insert
>
> INSERT INTO Imagetable VALUES (lo_import('C:\\image\\peach.jpg'));
have you succeeded in inserting an image into the database from a file
on the
server using the above query? AFAIK you need a bytea datatype, and also
the
image data has to be properly escaped (\\) before pg will accept it. I
assume
you are using a scripting language with a dbapi. Your steps would be:
1. read the image file into a variable
2. escape the image data
3. insert this into the database in a bytea field
if you follow this procedure it is irrelevant as to where the source
file is
- client or server
regards
kg
---(end of
broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Image Insert Postgresql DB
On Tuesday 23 November 2004 12:48 pm, Premsun Choltanwanich wrote: > I face this same problem when I try to insert image by lo_import to > database with my client files. I already change the way to bytea data > type but it's not work. The error return like type mismatch when I try > to insert it from Binary variable. > > Why I cannot insert it? If anybody found some solution please reply on > this topic for me too. you must escape the octets with \\ that is two backslashes and not one backslash. for example if your binary data is like this: \x05\x00\x02 you must make it like so: \\x05\\x00\\x02 please see chapter 8.4 of the postgresql manual. kg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
