Re: [GENERAL] create a table inside a function
I'm confused now because if i try my SQL command alone it works without any problem, but in the following pglSQL code, it does not :-( IF (outResult = 1) THEN > return true; > ELSE > EXECUTE 'create table sw.tmp_import (id serial NOT NULL CONSTRAINT id_key > PRIMARY KEY, software VARCHAR(1024), barcode VARCHAR(10), username > VARCHAR(1024), area VARCHAR(512), locality VARCHAR(512)) WITH > (OIDS=FALSE);'; > EXECUTE 'ALTER TABLE sw.tmp_import OWNER TO usr_audit'; > return false; > END IF; > why ? thx. A. On Thu, Jun 25, 2009 at 3:09 PM, Alain Roger wrote: > This is what i did at the beginning and it did not work. But now i've just > discovered that my create table command was wrong...so it's ok. :-) > thx. > > A. > > > On Thu, Jun 25, 2009 at 3:01 PM, Frank Heikens wrote: > >> A very simple and incomplete example: >> >> CREATE OR REPLACE FUNCTION new_table(int) returns bool AS >> $$ >> BEGIN >>IF $1 = 1 THEN >>EXECUTE 'CREATE TABLE x()'; >>ELSIF $1 = 2 THEN >>EXECUTE 'CREATE TABLE y()'; >>ELSE >>EXECUTE 'CREATE TABLE z()'; >>END IF; >> >>RETURN TRUE; >> END; >> $$ >> language plpgsql; >> >> SELECT new_table(1); >> >> >> >> Use EXECUTE and be sure you can't be the next victim of SQL injection. If >> you need some userinput in the EXECUTE-statement, use quote_literal() and/or >> quote_ident(). >> >> Regards, >> Frank >> >> Op 25 jun 2009, om 14:53 heeft Alain Roger het volgende geschreven: >> >> >> Hi, >>> >>> i would like to execute the following SQL command into a function based >>> on some IF, END IF tests before. >>> how can i do that ? >>> >>> here is my SQL command: >>> create table sw.tmp_import >>> ( >>> id serial NOT NULL, >>> software VARCHAR(1024), >>> barcode VARCHAR(10), >>> username VARCHAR(1024), >>> area VARCHAR(512), >>> locality VARCHAR(512) >>> CONSTRAINT id_pkey PRIMARY KEY (id)) >>> WITH (OIDS=FALSE); >>> >>> thanks a lot, >>> >>> Alain >>> --- >>> Windows XP x64 SP2 / Fedora 10 KDE 4.2 >>> PostgreSQL 8.3.5 / MS SQL server 2005 >>> Apache 2.2.10 >>> PHP 5.2.6 >>> C# 2005-2008 >>> >> >> > > > -- > Alain > --- > Windows XP x64 SP2 / Fedora 10 KDE 4.2 > PostgreSQL 8.3.5 / MS SQL server 2005 > Apache 2.2.10 > PHP 5.2.6 > C# 2005-2008 > -- Alain --- Windows XP x64 SP2 / Fedora 10 KDE 4.2 PostgreSQL 8.3.5 / MS SQL server 2005 Apache 2.2.10 PHP 5.2.6 C# 2005-2008
Re: [GENERAL] create a table inside a function
This is what i did at the beginning and it did not work. But now i've just discovered that my create table command was wrong...so it's ok. :-) thx. A. On Thu, Jun 25, 2009 at 3:01 PM, Frank Heikens wrote: > A very simple and incomplete example: > > CREATE OR REPLACE FUNCTION new_table(int) returns bool AS > $$ > BEGIN >IF $1 = 1 THEN >EXECUTE 'CREATE TABLE x()'; >ELSIF $1 = 2 THEN >EXECUTE 'CREATE TABLE y()'; >ELSE >EXECUTE 'CREATE TABLE z()'; >END IF; > >RETURN TRUE; > END; > $$ > language plpgsql; > > SELECT new_table(1); > > > > Use EXECUTE and be sure you can't be the next victim of SQL injection. If > you need some userinput in the EXECUTE-statement, use quote_literal() and/or > quote_ident(). > > Regards, > Frank > > Op 25 jun 2009, om 14:53 heeft Alain Roger het volgende geschreven: > > > Hi, >> >> i would like to execute the following SQL command into a function based on >> some IF, END IF tests before. >> how can i do that ? >> >> here is my SQL command: >> create table sw.tmp_import >> ( >> id serial NOT NULL, >> software VARCHAR(1024), >> barcode VARCHAR(10), >> username VARCHAR(1024), >> area VARCHAR(512), >> locality VARCHAR(512) >> CONSTRAINT id_pkey PRIMARY KEY (id)) >> WITH (OIDS=FALSE); >> >> thanks a lot, >> >> Alain >> --- >> Windows XP x64 SP2 / Fedora 10 KDE 4.2 >> PostgreSQL 8.3.5 / MS SQL server 2005 >> Apache 2.2.10 >> PHP 5.2.6 >> C# 2005-2008 >> > > -- Alain --- Windows XP x64 SP2 / Fedora 10 KDE 4.2 PostgreSQL 8.3.5 / MS SQL server 2005 Apache 2.2.10 PHP 5.2.6 C# 2005-2008
[GENERAL] create a table inside a function
Hi, i would like to execute the following SQL command into a function based on some IF, END IF tests before. how can i do that ? here is my SQL command: > create table sw.tmp_import ( id serial NOT NULL, software VARCHAR(1024), barcode VARCHAR(10), username VARCHAR(1024), area VARCHAR(512), locality VARCHAR(512) CONSTRAINT id_pkey PRIMARY KEY (id)) WITH (OIDS=FALSE); > thanks a lot, Alain --- Windows XP x64 SP2 / Fedora 10 KDE 4.2 PostgreSQL 8.3.5 / MS SQL server 2005 Apache 2.2.10 PHP 5.2.6 C# 2005-2008
[GENERAL] Fwd: concatenate and use as field
> > > Hi, >> >> i have a character varying variable and i concatenate with some other >> variable, using the '||' operator. >> the result of this concatenation should be the name of a column in my >> table. >> >> however i don't know how to tell that this new concatenated string is a >> column name. >> how to do it ? >> thanks. >> > > in fact i'm trying to do the following thing: > > select id, theme_ || $1 from themes; > > and based on the parameter my stored procedure has received, it should > create something like that: > select id, theme_eng from themes; > > if $1 = 'eng' > I think i'm a good way, but i still have a problem with the performance. when i call my stored procedure, it can take 0.1 s to 3.5 seconds to execute it on local computer. How can i improve it ? here is my stored procedure: CREATE OR REPLACE FUNCTION sewe.get_category_and_amount(character varying) RETURNS SETOF category_amount AS $BODY$ DECLARE inLanguage ALIAS FOR $1; outCategoryAndAmount category_amount; Lang character varying :=''; BEGIN IF inLanguage = null OR inLanguage = '' THEN Lang := 'eng'; ELSE Lang := inLanguage; END IF; FOR outCategoryAndAmount IN EXECUTE 'SELECT id, theme_name_' || Lang || ' FROM themes WHERE parent_theme IS NULL ORDER BY theme_name_' || Lang || ' ASC' LOOP RETURN NEXT outCategoryAndAmount; END LOOP; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 ROWS 1000;
[GENERAL] Fwd: concatenate and use as field
> > Hi, > > i have a character varying variable and i concatenate with some other > variable, using the '||' operator. > the result of this concatenation should be the name of a column in my > table. > > however i don't know how to tell that this new concatenated string is a > column name. > how to do it ? > thanks. > in fact i'm trying to do the following thing: select id, theme_ || $1 from themes; and based on the parameter my stored procedure has received, it should create something like that: select id, theme_eng from themes; if $1 = 'eng'
[GENERAL] concatenate and use as field
Hi, i have a character varying variable and i concatenate with some other variable, using the '||' operator. the result of this concatenation should be the name of a column in my table. however i don't know how to tell that this new concatenated string is a column name. how to do it ? thanks. -- Alain --- Windows XP x64 SP2 / Fedora 10 KDE 4.2 PostgreSQL 8.3.5 / MS SQL server 2005 Apache 2.2.10 PHP 5.2.6 C# 2005-2008
Re: [GENERAL] column name with plpgsql
Thanks a lot Raymond it works well. On Sat, Feb 7, 2009 at 3:32 PM, Raymond O'Donnell wrote: > On 07/02/2009 14:26, Alain Roger wrote: > > > i have a table which can be altered with several additional column. How > can > > i test under plpgsql if a particular column already exists ? > > select 1 from information_schema.columns > where table_name = 'your_table_name_here' > and column_name = 'your_column_name_here'; > > HTH, > > Ray. > > -- > Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland > r...@iol.ie > Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals > -- > -- Alain --- Windows XP x64 SP2 / Fedora 10 KDE 4.2 PostgreSQL 8.3.5 / MS SQL server 2005 Apache 2.2.10 PHP 5.2.6 C# 2005-2008
[GENERAL] column name with plpgsql
Hi, i have a table which can be altered with several additional column. How can i test under plpgsql if a particular column already exists ? thx. -- Alain --- Windows XP x64 SP2 / Fedora 10 KDE 4.2 PostgreSQL 8.3.5 / MS SQL server 2005 Apache 2.2.10 PHP 5.2.6 C# 2005-2008
[GENERAL] cost variable into function
Hi, i created a stored procedure (function) under postgreSQL and when i checked deeper into my DB, this is what i get: END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; ALTER FUNCTION myschema.get_count_languages(character varying) OWNER TO myuser; to what corresponds the parameter COST 100 ? it is the first time i see that :-( thx. -- Alain Windows XP SP3 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
[GENERAL] schema and roles
Hi, several days ago i wrote a post but i did not get any answer, so i'm asking again just in case of someone hasn't seen it. in fact i would like to know what are the default schemas allowed or active for a particular role. usually it's public and $user, but how to get those information ? i mean if i'm logged with the role "test_user", typing show search_path; will show me all default schemas. 1. is there another place (in som pg_... table) where this information is stored ? 2. if yes how can i get it ? After that, i know that using alter user test_user i can add another schema as default, but how to remove some ? thanks a lot, -- Alain Windows XP SP3 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
[GENERAL] user and default schema
Hi, where can i find which user account has which default schema ? thanks a lot, -- Alain Windows XP SP3 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
Re: [GENERAL] restore a dump db from tar file
> > From the docs: > > > -C > --create > >Begin the output with a command to create the database itself and > reconnect to the created database. (With a script of this form, it > doesn't matter which database you connect to before running the script.) > >This option is only meaningful for the plain-text format. For the > archive formats, you can specify the option when you call pg_restore. > > > So it seems that you can't do it when using a non-text format - you'll > have to do a plain-text dump, and maybe pipe it to tar if you want that. > > Ray. > Thanks Ray, this is also what i found later after my post :-( everything works well now.
Re: [GENERAL] restore a dump db from tar file
On Mon, Oct 6, 2008 at 3:12 PM, Raymond O'Donnell <[EMAIL PROTECTED]> wrote: > On 06/10/2008 14:03, Alain Roger wrote: > > this is basically what i use but it does not work. > > > > pg_restore -C -d sewe survey.tar -U postgres > > Just a guess, since I haven't used pg_restore - a quick look at the docs > suggests that the filename should come *last* on the command line, so > try that. > > Ray. > > -- > Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland > [EMAIL PROTECTED] > Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals > -- > AFAIK, pg_dump -v -o -U username -ci -Ft -f tarname.tar dbname backup the DB but without inserting code to create the BD itself... just to create its structure and populate it. noting about create database dbname grant. ... how can i do that automatically ? i mean when i dump my db, how can i dump ALSO the script to create the BD and its property ? thx.
Re: [GENERAL] restore a dump db from tar file
this is basically what i use but it does not work. > pg_restore -C -d sewe survey.tar -U postgres > but i get the following error message: pg_restore: [archiver (db)] connection to database "sewe" failed: FATAL: > passwo > rd authentication failed for user "raf_new" > where raf_new is my computer user and not user i used in my pg_restore :-( On Mon, Oct 6, 2008 at 2:07 PM, Raymond O'Donnell <[EMAIL PROTECTED]> wrote: > On 06/10/2008 09:07, Alain Roger wrote: > > > i backup my database "sewe" using a standard process. > > 1. it backups only database and not roles :-( roles are backuped > separately. > > 2. backup is a tar file > > 3. backup command is : pg_dump -v -o -U myuser -ci -Ft -f sewe.tar sewe > > > > Question > > how can i restore it now ? > > could something like that could work on Windows XP ? > > createdb sewe > > gunzip -c sewe.tar | psql sewe > > AAIU, the custom dump formats are less portable than the plain-text > format, so you might be better off using that. In that case, you need to > unzip the file first and then restore it using psql. > > Ray. > > -- > Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland > [EMAIL PROTECTED] > Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals > -- > -- Alain Windows XP SP3 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
[GENERAL] restore a dump db from tar file
Hi, i backup my database "sewe" using a standard process. 1. it backups only database and not roles :-( roles are backuped separately. 2. backup is a tar file 3. backup command is : pg_dump -v -o -U myuser -ci -Ft -f sewe.tar sewe Question how can i restore it now ? could something like that could work on Windows XP ? createdb sewe gunzip -c sewe.tar | psql sewe -- Alain Windows XP SP3 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
Re: [GENERAL] sequence... my nightmare :-(
if i double-quote it, postgre tells me that the column accounts_id_seq does
not exist.
On Sat, Sep 27, 2008 at 5:59 PM, Glyn Astill <[EMAIL PROTECTED]> wrote:
> >
> > when i write the following query i get the error :ERROR:
> > relation
> > "accounts_id_seq" does not exist
> > SET search_path = test_survey;
> > insert into accounts values
> > (nextval('accounts_ID_seq'),'[EMAIL PROTECTED]
> ','ab4ef51934f2d3f02f1a','11/19/2007
> > 15:46:09');
> >
> > why ?
> >
>
> It's lowercasing the relation name, note "accounts_id_seq" not
> "accounts_ID_seq". Try double quoting it.
>
>
>
>
--
Alain
Windows XP SP3
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
[GENERAL] sequence... my nightmare :-(
Hi,
i have a schema called : test_survey
i have within this schema this table:
CREATE TABLE test_survey.accounts
(
"ID" bigint NOT NULL DEFAULT
nextval('test_survey."accounts_ID_seq"'::regclass),
"login" character varying(300) NOT NULL,
pwd character varying(100) NOT NULL,
creation_date timestamp without time zone NOT NULL,
CONSTRAINT accounts_pkey PRIMARY KEY ("ID")
)
WITH (OIDS=FALSE);
ALTER TABLE test_survey.accounts OWNER TO mysurvey;
and this sequence:
CREATE SEQUENCE test_survey."accounts_ID_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE test_survey."accounts_ID_seq" OWNER TO mysurvey;
when i write the following query i get the error :ERROR: relation
"accounts_id_seq" does not exist
SET search_path = test_survey;
insert into accounts values
(nextval('accounts_ID_seq'),'[EMAIL
PROTECTED]','ab4ef51934f2d3f02f1a','11/19/2007
15:46:09');
why ?
--
Alain
Windows XP SP3
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
[GENERAL] only the last 3 records
Hi, maybe it's a stupid question, but i do not remember how to query a table and to request only the latest 3 added records. How can i do that (i have a field time and a field date) ? thx. -- Alain Windows XP SP3 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
Re: [GENERAL] dunction issue
I do not agree with you Sam.
Stored procedure are safe from hacking (from external access).
>From my point of view transitions should be used only as internal purpose or
via intrAnet and not thru intErnet.
at list this is how under MS SQL they use to teach.
regarding unique constraint, i already setup it. :-)
unique violation will not help me in this case, or only to know if the email
is stored several time which i do not test...directly :-)
Al.
On Fri, Mar 28, 2008 at 1:19 AM, Sam Mason <[EMAIL PROTECTED]> wrote:
> On Thu, Mar 27, 2008 at 03:34:49PM -0700, Adrian Klaver wrote:
> > Or a simpler way to do handle my previous suggestion:
> >
> > IF (ret_email IS NULL ) OR (ret_email='') THEN
> > RETURN ('-3')
>
> That would be the sane fix, yes.
>
> Based on the previous emails from the OP, he seems to be missing a
> lot of the tools that databases' give you. Transactions and unique
> constraints being two significant ones. Writing stored procedures to do
> their work is just going to introduce unnecessary bugs and complication.
>
>
> Sam
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Alain
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
Re: [GENERAL] pl/pgsql and controling loops
this is what i did, and it's true that '==' does not exist under pl/pgsql. Only '=' should be used. On Thu, Mar 27, 2008 at 8:53 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] (Adrian Klaver) writes: > > From: "Alain Roger" <[EMAIL PROTECTED]> > >> once to do a comparison they use : > > IF (ret == 1) THEN ... > >> once, > > IF (ret = 1) THEN... > > > Both are correct. > > No they're not, as a simple test would convince you ... > there is no '==' operator in SQL. > >regards, tom lane > -- Alain Windows XP SP2 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
[GENERAL] dunction issue
Hi,
i have a problem solving my function trouble.
this function should return an email address stored in a table
(tmp_newsletterreg) based on a number (sessions ID).
if the session id is not find it should return a string corresponding to and
error.
if the email in found but already exists into another table (users), it
should also return a string value relative the this error.
here is my stored procedure.
> CREATE OR REPLACE FUNCTION cust_portal.sp_u_002(id_session character
> varying)
> RETURNS character varying AS
> $BODY$
>
> DECLARE
>
> ret_email CHARACTER VARYING(512) :='';
> usr_exists INTEGER := 0;
> usr_exists_2 INTEGER := 0;
>
> BEGIN
> set search_path = cust_portal;
>
> SELECT count(*) INTO usr_exists FROM tmp_newsletterreg WHERE
> tmp_usr_id = id_session;
> IF (usr_exists = 1) THEN
> SELECT email INTO ret_email FROM tmp_newsletterreg WHERE
> tmp_usr_id = id_session;
> IF (ret_email IS NULL || ret_email='') THEN
> RETURN ('-3');
> ELSE
> SELECT count(*) INTO usr_exists_2 FROM users WHERE users.email=
> ret_email;
> IF (usr_exists_2 = 0) THEN -- first try of user to get
> registered
> RETURN (ret_email);
> ELSE -- user already exists into users tables (several
> tries to register)
>RETURN ('-2');
> END IF;
> END IF;
> ELSE
> RETURN('-1');
> END IF;
> END;
>
if the session id is wrong, it works correctly.
however if the session id is correct it does not return me the email address
(even if it really exist into table tmp_newsletterreg / but not in table
users.)
so i think my eyes are tired, because i do not see an error...
thanks.
--
Alain
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
[GENERAL] pl/pgsql and controling loops
Hi, i've read several books extract about controlling loops in postgreSQL under pl/pgsql and there is something interesting. once to do a comparison they use : > IF (ret == 1) THEN ... once, > IF (ret = 1) THEN... so are they both correct ? comming from .NET/C++ world, usually we use '==' or '!=', so i expect that correct ones are '==' and '<>'. moreover, when i check if the returned value "ret_email" from SELECT email INTO ret_email FROM tmp_newsletterreg WHERE tmp_usr_id = id_session; i want to check if it is empty or not, so i do IF(ret_email <> '' ) THEN... but it does not work... every time it enter in the loop, even if it is empty string. where is the problem ? thx. -- Alain Windows XP SP2 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
Re: [GENERAL] returned value from SQL statement
not really.. but it is true that it can be confusing...sorry :-(
the purpose here, it is to solve my problem with a transaction inside a
function.
i need to know if there is a common return value for error in case of a SQL
statement failed.
it seems that not, so i would like to know if the rollback inside an
EXCEPTION block is the best practice.
here is an example :
> CREATE OR REPLACE FUNCTION cust_portal.sp_u_003(usrname character varying,
> firstname character varying, email character varying, nl_reg boolean,
> nl_lang character varying)
> RETURNS integer AS
> $BODY$
>
> DECLARE
>
> existing_email INTEGER := 0;
>
> BEGIN
> set search_path = cust_portal;
>
> SELECT count(*) INTO existing_email FROM users WHERE users.email =
> email;
> IF (existing_email != 0) THEN
> RETURN (-1);
> ELSE
> --BEGIN TRANSACTION;
> INSERT INTO cust_portal.users VALUES
> (
> nextval('users_usr_id_seq'),
> usrname,
> firstname,
> email,
> nlreg,
> nl_lang
> );
>
> DELETE FROM cust_portal.tmp_newsletterreg WHERE
> tmp_newsletterreg.email = email;
>
> COMMIT;
> RETURN(0);
>
> EXCEPTION
> ROLLBACK;
> RETURN(-2);
>
> END IF;
> END;
>
moreover such code generates an error :
On Thu, Mar 27, 2008 at 10:55 AM, Craig Ringer <[EMAIL PROTECTED]>
wrote:
> Alain Roger wrote:
> > sorry... under pl/pgsql as stored procedure
>
> Then this question was already asked and answered less than a week ago
> on this mailing list.
>
> By you, with almost exactly the same subject line.
>
> Forgive my confusion, but why are you asking the same question again?
> What's changed since last time? What else do you need to know?
>
> --
> Craig Ringer
>
--
Alain
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
Re: [GENERAL] returned value from SQL statement
thanks for the link regarding errors... i did not find it before. On Thu, Mar 27, 2008 at 10:47 AM, Richard Huxton <[EMAIL PROTECTED]> wrote: > Alain Roger wrote: > > hi, > > > > i would like to know how can i control the returned value from a SQL > > statement ? > > for example, if i do an INSERT INTO... : > > 1. how can i know if the INSERT worked ? > > i mean i already trapped the unicity violation, but what if there is > another > > error ? where can i get a complete list of exceptions ? > > > > 2. if the INSERT INTO is successful, how can i know it ? what is the > > returned value ? > > If it fails you'll get an error. The transaction will be aborted. How > you access this error code will depend on what library/client you are > using. > > > 3. the same question as point 2 but for all SQL statements from > PostgreSQL. > > Same. > > > I read few books and every time this part is not treated correctly but > only > > partially. > > There's a full list of error codes here if you want to know what went > wrong: > http://www.postgresql.org/docs/current/static/errcodes-appendix.html > > Am I mis-understanding you? > > -- > Richard Huxton > Archonet Ltd > -- Alain Windows XP SP2 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
Re: [GENERAL] returned value from SQL statement
sorry... under pl/pgsql as stored procedure On Thu, Mar 27, 2008 at 10:46 AM, Craig Ringer <[EMAIL PROTECTED]> wrote: > Alain Roger wrote: > > hi, > > > > i would like to know how can i control the returned value from a SQL > > statement ? > > > > You've left out some pretty significant information, like what method > you're using to access PostgreSQL. > > ODBC from C/C++/whatever? > > PHP? > > Perl DBD? > > Python DBI? > > The .net interfaces? > > Java / JDBC ? > > Java with an ORM layer ? > > PL/PgSQL stored proceures executed by `psql' ? > > ... > > -- > Craig Ringer > -- Alain Windows XP SP2 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
[GENERAL] returned value from SQL statement
hi, i would like to know how can i control the returned value from a SQL statement ? for example, if i do an INSERT INTO... : 1. how can i know if the INSERT worked ? i mean i already trapped the unicity violation, but what if there is another error ? where can i get a complete list of exceptions ? 2. if the INSERT INTO is successful, how can i know it ? what is the returned value ? 3. the same question as point 2 but for all SQL statements from PostgreSQL. I read few books and every time this part is not treated correctly but only partially. thanks a lot, -- Alain Windows XP SP2 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
Re: [GENERAL] return value from SQL statement
under pl/pgsql language i would like to return a function value. e.g : 0 is everything is completed -1 if searched data already exist into table -2 if insert into failed ... later i would like to use this returned value in my PHP (this i know how to do) is it clearer ? A. On Tue, Mar 25, 2008 at 3:02 PM, Craig Ringer <[EMAIL PROTECTED]> wrote: > Alain Roger wrote: > > Hi, > > > > i have a 'insert into' statement and i would like to know if it fails or > > not. > > i was thinking to check the resturn value from this statement, but how > to do > > it ? > > > > > The answer depends entirely on how you're using the database. > > ODBC? > > JDBC? > > Perl DBD? > > Python with psycopg? > > SQL code read by the psql command? > > Also, by "failure", do you mean "encountered an error that terminated > the transaction" or "inserted zero rows" ? > > -- > Craig Ringer > -- Alain Windows XP SP2 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
[GENERAL] return value from SQL statement
Hi, i have a 'insert into' statement and i would like to know if it fails or not. i was thinking to check the resturn value from this statement, but how to do it ? thx. -- Alain Windows XP SP2 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
Re: [GENERAL] date and time
So thanks a lot to everybody... so here is the result.
1. the semicolon was missing after the INSERT as wrote Raymond.
2. CURRENT_TIMESTAMP works great
3. i use pl/pgsql as language
thanks again.
Alain
On Mon, Mar 24, 2008 at 3:56 PM, Thomas Kellerer <[EMAIL PROTECTED]> wrote:
> Alain Roger wrote on 24.03.2008 15:45:
> >> SELECT count(*) INTO existing_email FROM cust_portal.tmp_newsletterreg;
> >> if (existing_email <>0) then
> >> {
> >> result = false;
> >> }
> >> else
> >> {
> >> result = true;
> >> INSERT INTO cust_portal.tmp_newsletterreg VALUES
> >> (
> >> nextval( 'tmp_newsletterreg_nlreg_id_seq' ),
> >> email,
> >> session,
> >> SELECT CURRENT_TIMESTAMP;
> >> )
> >> }
> >> end if;
> >>
> >> RETURN(result);
>
>
> That should be
>
> INSERT INTO cust_portal.tmp_newsletterreg VALUES
> (
> nextval( 'tmp_newsletterreg_nlreg_id_seq' ),
> email,
> session,
> CURRENT_TIMESTAMP
> )
>
> There is no SELECT and no semicolon inside the INSERT
>
> Thomas
>
>
> -
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Alain
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
Re: [GENERAL] date and time
Hi Ray,
yes for sure. Here it is:
> SELECT count(*) INTO existing_email FROM cust_portal.tmp_newsletterreg;
> if (existing_email <>0) then
> {
> result = false;
> }
> else
> {
> result = true;
> INSERT INTO cust_portal.tmp_newsletterreg VALUES
> (
> nextval( 'tmp_newsletterreg_nlreg_id_seq' ),
> email,
> session,
> SELECT CURRENT_TIMESTAMP;
> )
> }
> end if;
>
> RETURN(result);
>
On Mon, Mar 24, 2008 at 3:42 PM, Raymond O'Donnell <[EMAIL PROTECTED]> wrote:
> On 24/03/2008 14:35, Alain Roger wrote:
> > for that i use "select * from now();" and store the result into a column
> > table.
> >
> > is there a easier way to do that ? i tried to store directly now();
> > result but without success.
>
> Can you show us the full SQL statement?
>
> You could also use CURRENT_TIMESTAMP - look at the following:
>
>
> http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
>
> HTH,
>
> Ray.
>
>
> ---
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> [EMAIL PROTECTED]
> ---
>
--
Alain
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
[GENERAL] date and time
Hi, i have a stored procedure (a function) in which i must generate a date/time stamp. for that i use "select * from now();" and store the result into a column table. is there a easier way to do that ? i tried to store directly now(); result but without success. thx. -- Alain Windows XP SP2 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
[GENERAL] scheduling stored procedure
Hi, I have a temporary table in which i store all emails of users who want to receive a newsletter. to avoid spamming and to limit the size of this table, i would like to delete all requests (records) which are older than 72 hours. i was thinking to execute each hour a stored procedure which will check the time and date (of now) if it is greater than "registration request time" stored in this temporary table. if it is greater, so record will be deleted. does postgresql have such timer (something like "cron" under unix/linux) ? thanks a lot, -- Alain Windows XP SP2 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
[GENERAL] insert into...
Hi,
i would like to understand why the following INSERT INTO statement works :
INSERT INTO mytable
SELECT nextval('my_sequence'),
'myname',
'myfirstname'
;
whereas usually we should do :
INSERT INTO mytable
VALUES
(
SELECT nextval('my_sequence'),
'myname',
'myfirstname'
);
thanks a lot,
--
Alain
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
Re: [GENERAL] sequence
Hi Tom,
but when i let pgsql setup everything (i mean when i create table -> pgsql
creates sequence)
), i have called = no, before using any select nextval()...
and in this case, it works great.
but once called = yes, select nextval(sequence_name); always gives me
current value +1 :-(
A.
On Dec 9, 2007 4:51 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Alain Roger" <[EMAIL PROTECTED]> writes:
> > to perform an autoincrement in my SQL queries...specially while i use
> insert
> > into i do the following thing :
>
> > INSERT INTO mytable VALUES
> > (
> > select nextval('users_user_id_seq'),
> > ...
> > );
>
> > however this get the currentvalue + 1, or during creating the sequence i
> > must say that start = 0.
>
> Really? Works fine for me:
>
> regression=# create sequence foo start with 10;
> CREATE SEQUENCE
> regression=# select nextval('foo');
> nextval
> -
> 10
> (1 row)
>
> regression=# select nextval('foo');
> nextval
> -
> 11
> (1 row)
>
> If you're initializing the sequence some other way, such as with
> setval(), maybe you need to make use of the is_called option to setval().
>
>regards, tom lane
>
--
Alain
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
[GENERAL] sequence
Hi,
I'm still trying to understand how the sequences work under PostgreSQL.
for example i have a sequence called : users_user_id_seq
with :
current value = 1
min value = 1
max value = 9223372036854775807
start = 1
a typical "serial" field.
to perform an autoincrement in my SQL queries...specially while i use insert
into i do the following thing :
INSERT INTO mytable VALUES
(
select nextval('users_user_id_seq'),
...
);
however this get the currentvalue + 1, or during creating the sequence i
must say that start = 0.
how can i get the current value (to use it into my "insert into statement")
and by the same time,. increment it by 1, to be ready for the next time ?
--
Alain
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
Re: [GENERAL] list of roles
Hi / Cau Pavel, It works great / funguje vyborne Alain On Dec 8, 2007 1:38 PM, Pavel Stehule <[EMAIL PROTECTED]> wrote: > On 08/12/2007, Alain Roger <[EMAIL PROTECTED]> wrote: > > Hi, > > > > i would like to know how can i get the complete list of roles available > ? > > as i use phppgAdmin, it could be great to get it as a SQL query. > > > > thanks a lot, > > > > Hello > > SELECT rolname FROM pg_roles; > > Regards > Pavel Stehule > > > -- > > Alain > > > > Windows XP SP2 > > PostgreSQL 8.2.3 > > Apache 2.2.4 > > PHP 5.2.3 > -- Alain Windows XP SP2 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
[GENERAL] list of roles
Hi, i would like to know how can i get the complete list of roles available ? as i use phppgAdmin, it could be great to get it as a SQL query. thanks a lot, -- Alain Windows XP SP2 PostgreSQL 8.2.3 Apache 2.2.4 PHP 5.2.3
[GENERAL] check if database is correctly created
Hi, i would like to check (via PHP or C#) if my database has been correctly created. for that i use the following SQL : select * from pg_tables where tablename = 'xxx' AND schemaname = 'yyy'; this i repeat till i check all tables. But how to check sequences, index, functions, and so on ? thanks a lot, -- Alain Windows XP SP2 PostgreSQL 8.2.3 Apache 2.2.4 PHP 5.2.3
[GENERAL] import content of XLS file into PostgreSQL
Hi, I would like to know what should i do to import the content (not all columns) of a XLS file into pgsql. is there something special to do ? thanks a lot, -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.2.4 PHP 5.2.1
[GENERAL] function and bytea
Hi,
In my PHP page i upload pictures into database. For that i wrote a simple
function which control which user is logged and will upload his picture.
here is the function :
CREATE OR REPLACE FUNCTION sp_a_006("login" character varying, photo bytea)
RETURNS boolean AS
$BODY$
DECLARE
my_idINTEGER :=0;
BEGIN
select into my_id
account_id from accounts where account_login = $1;
IF (my_id != 0) THEN
UPDATE users
SET user_photo = $2
WHERE user_account_id = my_id;
RETURN (TRUE);
ELSE
RETURN (FALSE);
end if;
END;
when i call this function in PHP, i do the following :
$my_query = "select * from immense.sp_a_006
('".$_SESSION["username"]."','{$escaped}')";
$res_pic = pg_query(my_query);
where $escaped = pg_escape_bytea($data);
this inserts the picture only if i add E in front of '{$escaped}' and
becomes E'{$escaped}').
why ?
on another website i do not use function but a simple SQL query as following
and it works :
pg_query("INSERT INTO photo (photo_id,document_orientation_id, photo_date,
photo_image)
VALUES
(nextval('photo_photo_id_seq'),".$orientation_id[0].",NOW(),
'{$escaped}')");
thanks a lot,
--
Alain
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5
[GENERAL] issue when export/import table including pictures
Hi,
As i did not get any answer yesterday i'm asking it again.
I have a table with pictures stored as bytea field.
I'm able to export them from my database hosted by my web supplier.
However, when i try to import them to my local database, i get 2 types of
error message (based on how i import them)
1. if import is done by SQL -> run SQL script in phppgadmin : error message
is " No server supplied!"
2. if import is done by Import function from a table in phppgadmin : error
message is "Import error: File could not be uploaded to the server"
here is a sample of my export table in SQL format :
--SET client_encoding = 'UTF8';
--SET standard_conforming_strings = off;
--SET check_function_bodies = false;
--SET client_min_messages = warning;
--SET escape_string_warning = off;
SET search_path = immense, pg_catalog;
SELECT pg_catalog.setval('photo_photo_id_seq', 30, true);
INSERT INTO photo VALUES (1, 1, '2007-02-25 00:00:00',
'\\377\\330\\377\\3412\\004Exif\\000 . 32E\\303\\215\\235?\\377\\331');
i truncated the last field due to its length :-)
SO as i have such problem with phppgadmin, i use pgadmin GUI under windows.
But pgadmin deletes the bytea information in thie INSERT INTO SQL command
:-( and i do not know why.
what could be the problem ?
thanks for your help.
--
Alain
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5
Re: [GENERAL] postgresql 8.1.4 to 8.2.3
After clicking on your link i got "invalid project" page :-( and the whole page is empty... On 4/14/07, Erik Jones <[EMAIL PROTECTED]> wrote: On Apr 14, 2007, at 11:38 AM, Alain Roger wrote: > thanks for the info. > anyway i was thinking to do that, but i wanted to be sure. > > On 4/14/07, Anton Melser < [EMAIL PROTECTED]> wrote:On > 14/04/07, Alain Roger < [EMAIL PROTECTED]> wrote: > > Hi, > > > > My web host upgrade his postgreSQL version to 8.2.3 so i would > like to do > > the same on my local computer where i develop. > > Is there something particular to do ? > > or can I just run the exe file (on windows) without doing a > complete backup > > (pgdump) before ? > > > > thanks a lot, > > RTFM :-). Between major versions (8.1 -> 8.2) you DO need to dump and > reload. So do that... Actually, this isn't strictly true: http://pgfoundry.org/projects/pg- migrator/ erik jones <[EMAIL PROTECTED]> software developer 615-296-0838 emma(r) -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5
[GENERAL] phppgadmin and dump
Hi, I did a copy export from my web hosted database. I did an export in COPY and SQL mode for 1.data and 2.structure only When i try to import the data, phppgadmin raises an error : "No server supplied!" what could be the reason ? I was thinking about those picture i have in DB like '\\37\\" but i do not see any reason. thanks for your help. -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5
Re: [GENERAL] postgresql 8.1.4 to 8.2.3
thanks for the info. anyway i was thinking to do that, but i wanted to be sure. On 4/14/07, Anton Melser <[EMAIL PROTECTED]> wrote: On 14/04/07, Alain Roger <[EMAIL PROTECTED]> wrote: > Hi, > > My web host upgrade his postgreSQL version to 8.2.3 so i would like to do > the same on my local computer where i develop. > Is there something particular to do ? > or can I just run the exe file (on windows) without doing a complete backup > (pgdump) before ? > > thanks a lot, RTFM :-). Between major versions (8.1 -> 8.2) you DO need to dump and reload. So do that... Cheers Anton -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5
[GENERAL] postgresql 8.1.4 to 8.2.3
Hi, My web host upgrade his postgreSQL version to 8.2.3 so i would like to do the same on my local computer where i develop. Is there something particular to do ? or can I just run the exe file (on windows) without doing a complete backup (pgdump) before ? thanks a lot, -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5
[GENERAL] how to know a table size ?
Hi, I would like display in my PHP application the size of each table. So, how can i get the table size (for example in Mb) ? thanks a lot, -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5
[GENERAL] get value after updating table
Hi,
I wrote a function which should update a table field.
However, i would like somehow to control that update was done.
for that i was thinking to return a boolean : true is update was done, false
if an error happened.
however, i can i do that ? I mean how can i know if UPDATE has been
correctly executed ?
here is my function :
CREATE OR REPLACE FUNCTION sp_a_005("login" "varchar", pwd "varchar")
RETURNS boolean AS
$BODY$
DECLARE
BEGIN
UPDATE accounts
SET account_pwd = $2
WHERE account_login = $1;
RETURN;
END;
--
Alain
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5
[GENERAL] Stored Procedure / function and their result
Hi,
I would like to know if there is a better way how to retrieve result from a
stored procedure (function) than to use 'AS res(col1 varchar, col2
timestamp,..)'
for example, here is a stored procedure :
CREATE OR REPLACE FUNCTION SP_A_003(username VARCHAR)
RETURNS SETOF RECORD AS
$BODY$
DECLARE
myrec RECORD;
BEGIN
FOR myrec IN
select
users.user_name,
users.user_firstname,
accounts.account_login,
statususer.statususer_type
from accounts, users, statususer
where
accounts.account_login = $1
AND
accounts.account_id = users.user_account_id
AND
users.user_status_id = statususer.statususer_id
LOOP
RETURN NEXT myrec;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
...
here is how i call it :
select * from sp_a_003('my_user_name')
as result
(
name varchar,
firstname varchar,
userlogin varchar,
statustype varchar
);
to understand well, in my stored procedure i only select a part of each
table (so i build a "composite" record) therefore i understood that SETOF
RECORD AS was the best solution for that.
however the result call is catastrophic when stored procedure returns
several fields. when it is more than 2 fields i'm already "angry" to write :
as result
(
name varchar,
firstname varchar,
userlogin varchar,
statustype varchar,
);
I would like to avoid this "as result (...)", so is there a better solution
?
thanks a lot,
--
Alain
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5
Re: [GENERAL] issue with SELECT settval(..);
thanks a lot Christian. On 3/18/07, Christian Schröder <[EMAIL PROTECTED]> wrote: Alain Roger wrote: > insert into immense.statususer (statususer_id, statususer_type) values > (SELECT nextval( 'statususer_statususer_id_seq' ),'customer'); The correct syntax would be: insert into immense.statususer (statususer_id, statususer_type) values ((SELECT nextval( 'statususer_statususer_id_seq' )),'customer'); The sub-select must be put in parentheses. However, the much simpler statement insert into immense.statususer (statususer_id, statususer_type) values (nextval( 'statususer_statususer_id_seq' ),'customer'); will do the same without a sub-select. Regards Christian -- Deriva GmbH Tel.: +49 551 489500-42 Financial IT and Consulting Fax: +49 551 489500-91 Hans-Böckler-Straße 2 http://www.deriva.de D-37079 Göttingen Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer ---(end of broadcast)--- TIP 1: 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 -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5
[GENERAL] issue with SELECT settval(..);
Hi, I would like to auto increment my sequence when i'm going to insert a new record into my table. for that i wanted to use : insert into immense.statususer (statususer_id, statususer_type) values (SELECT nextval( 'statususer_statususer_id_seq' ),'customer'); however, i get an error message in pgAdmin III, as following : ERROR: syntax error at or near "SELECT" at character 168 i do not under because if I run SELECT nextval( 'statususer_statususer_id_seq' ) alone, it works perfectly and increment the sequence. moreover, everything regarding the table is correct. so where is the problem ? thanks a lot, -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5
[GENERAL] Stored procedure
Hi,
I have a stored procedure which returns a SETOF RECORD.
so basically a partial rowtype from a table.
to execute the query in PHP, i must write :
select * from myschema.sp_a_002('username') as result(Column1 varchar);
to get the result.
However, is there another to get the result without using 'as result(column1
varchar)' ?
something like a simple "select * from storedprocedure(param);", for example
:-)
thanks a lot,
--
Alain
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5
[GENERAL] question about stored procedure / function
Hi, i created the following function : -- Function: immense.sp_a_001(username "varchar", pwd "varchar") -- DROP FUNCTION immense.sp_a_001(username "varchar", pwd "varchar"); CREATE OR REPLACE FUNCTION immense.sp_a_001(username "varchar", pwd "varchar") RETURNS int4 AS $BODY$ DECLARE myrec immense.accounts%ROWTYPE; count INTEGER := 0; /**/ BEGIN FOR myrec IN SELECT * FROM immense.accounts WHERE account_login=$1 and account_pwd=$2 LOOP count := count + 1; END LOOP; RETURN count; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION immense.sp_a_001(username "varchar", pwd "varchar") OWNER TO immensesk; GRANT EXECUTE ON FUNCTION immense.sp_a_001(username "varchar", pwd "varchar") TO immensesk; However, postgreSQL add automatically the following line to each procedure and i do not know why ? GRANT EXECUTE ON FUNCTION immense.sp_a_001(username "varchar", pwd "varchar") TO public; normally, in such case (i mean without granted execution right to public on this procedure), only immensesk user should be able to run it... so why such thing ? it is not secured... or is there something i missed ? -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5
[GENERAL] issue with select currval
Hi,
I have a table "photo" where my primary key is name "photo_id" (only serial
value is stored there).
when i type :
select currval('photo_photo_id_seq');
the answer is :
ERROR: relation "photo_photo_id_seq" does not exist
whereas the "relation photo_photo_id_seq" exists
So where is the problem ? should i specify the schema ? (schema is :
immense)
if i type :
SELECT photo_id FROM immense.photo ORDER BY photo_id DESC LIMIT 1
i get what i want (the last serial/index value used by my table)
thanks for help.
--
Alain
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
This is what i did (to stored pictures in DB)... but i use the following process : 1.store picture on my localhost db 2. export as SQL statement all pictures from my table :-( ===> it was 7.4Mb 3. import to the remote db hosted by a company. is there an easy way to store image into a hosted DB ? because i can not use any tool to directly insert into DB the pictures :-( they only accept pgadmin interface... thanks a lot, Al. On 2/25/07, Tomas Vondra <[EMAIL PROTECTED]> wrote: > Store the pictures in the filesystem and only the path, description and > other metadata in the database. My suggestion ;-) > > > Andreas > Don't do that - the filesystems are not transactional (at least not the usual ones), so you'll lose the ability to use transactions. Imagine what happens when you do an unlink() and then the transaction fails for some reason - there's no way to 'rollback' the filesystem operation. I've seen this solution (storing images in filesystem) mostly in MySQL applications, but that's because of (a) lack of transactions in MySQL and (b) somehow sub-optimal handling of binary data as MySQL loads all the data even if it's not needed (this was true for MySQL 3.23 - I'm not sure about the current releases). Anyway, I do recommend storing images in the database, using a 'bytea' column for the binary data (and load them only if reallly needed, using proper projection). You can do some benchmarks, but I've never head performance problems with it on PostgreSQL and the ability to use transactions was invaluable (it saved us hundreds of hours when the machine went down for some reason). Tomas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5
[GENERAL] Best way to store and retrieve photo from PostGreSQL
Hi, I would like to store picture in my DB and after to display them on my PHP pages. What is the best solution for that ? thanks a lot -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5
Re: [GENERAL] I'm lost :-( with FOR...IN
Ok guys...i found the stupid problem :-((everytime that i call my stored procedure, i did like that : select sp_u_001('action'); instead of select * from sp_u_001('action');thanks to all of you for your tips, they helped me to understand composite.
one last question : how can i test if myrec composite has some records ?i was thinking about == > select count(*) from (myrec); but i'm not sure about the logic of this command.Al.
On 11/7/06, Alain Roger <[EMAIL PROTECTED]> wrote:
i already tried this possibility and i've got :ERROR: set-valued function called in context that cannot accept a setCONTEXT: PL/pgSQL function "sp_u_001" line 26 at return next:-(
On 11/7/06, Stephan Szabo <[EMAIL PROTECTED]> wrote:
On Tue, 7 Nov 2006, Alain Roger wrote:> Hi,>> I' still with my stored procedure :>> -- Function: SP_U_001(typeofarticle varchar)>> -- DROP FUNCTION SP_U_001(typeofarticle varchar);
>> CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR)> RETURNS SETOF active_articles AS> $BODY$> DECLARE> myrec RECORD;> res active_articles;> /**/
> BEGIN> FOR myrec IN> select *> from articles, articletypes, department> where> articletypes.articletype_type = $1> AND articles.articletype_id =
articletypes.articletype_id> AND articles.department_id = department.department_id> AND articles.validity_period_end > now()> LOOP> IF (myrec IS NOT NULL) THEN>
res.article_type := myrec.articletypes.articletype_type;I don't think the column names are going to keep their originating tablename inside the record, so the field probably needsto be referred to as myrec.articletype_type
notmyrec.articletypes.articletype_type.
Re: [GENERAL] I'm lost :-( with FOR...IN
i already tried this possibility and i've got :ERROR: set-valued function called in context that cannot accept a setCONTEXT: PL/pgSQL function "sp_u_001" line 26 at return next:-( On 11/7/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: On Tue, 7 Nov 2006, Alain Roger wrote:> Hi,>> I' still with my stored procedure :>> -- Function: SP_U_001(typeofarticle varchar)>> -- DROP FUNCTION SP_U_001(typeofarticle varchar); >> CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR)> RETURNS SETOF active_articles AS> $BODY$> DECLARE> myrec RECORD;> res active_articles;> /**/ > BEGIN> FOR myrec IN> select *> from articles, articletypes, department> where> articletypes.articletype_type = $1> AND articles.articletype_id = articletypes.articletype_id> AND articles.department_id = department.department_id> AND articles.validity_period_end > now()> LOOP> IF (myrec IS NOT NULL) THEN> res.article_type := myrec.articletypes.articletype_type;I don't think the column names are going to keep their originating tablename inside the record, so the field probably needsto be referred to as myrec.articletype_type notmyrec.articletypes.articletype_type.
Re: [GENERAL] I'm lost :-( with FOR...IN
If i do what you wrote, i can not create the function into my DB.error on 1st ( On 11/7/06, Merlin Moncure < [EMAIL PROTECTED]> wrote:On 11/7/06, Alain Roger < [EMAIL PROTECTED]> wrote:> Hi,>> I' still with my stored procedure :>> -- Function: SP_U_001(typeofarticle varchar)>> -- DROP FUNCTION SP_U_001(typeofarticle varchar); >> CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR)> RETURNS SETOF active_articles AS> $BODY$> DECLARE> myrec RECORD;> res active_articles;> /**/ > BEGIN> FOR myrec IN> select *> from articles, articletypes, department> where> articletypes.articletype_type = $1> AND articles.articletype_id = articletypes.articletype_id> AND articles.department_id = department.department_id> AND articles.validity_period_end > now()> LOOP> IF (myrec IS NOT NULL) THEN> res.article_type :=> myrec.articletypes.articletype_type;> res.article_author := myrec.articles.author;> res.department_owner :=> myrec.department.department_name ;> res.department_picture :=> myrec.department.department_picture;> res.article_title := myrec.articles.title;> res.article_content := myrec.articles.content;> res.date_creation := myrec.articles.creation_date ;> res.date_start :=> myrec.articles.validity_period_start;> res.date_end := myrec.articles.validity_period_end;> END IF;> RETURN NEXT res; > END LOOP;> RETURN;> END;> $BODY$> LANGUAGE 'plpgsql' VOLATILE;> ALTER FUNCTION SP_U_001(VARCHAR) OWNER TO immensesk;> GRANT EXECUTE ON FUNCTION SP_U_001(VARCHAR) TO immensesk; >> whatever, i do the argument VARCHAR will be stored in double quote as> "varchar" when i check via pgAdmin GUI. Even if in command line it looks> like above.>> I still have the same error message on "myrec" : > ERROR: schema "myrec" does not exist> CONTEXT: SQL statement "SELECT> myrec.articletypes.articletype_type"you are using composite types right? you have to add parenthesis to disambiguate this case:http://www.postgresql.org/docs/8.1/interactive/rowtypes.html#AEN5789res.article_title := myrec.(articles).title; sorry i missed that the first time out.merlin
[GENERAL] I'm lost :-( with FOR...IN
Hi,I' still with my stored procedure :-- Function: SP_U_001(typeofarticle varchar)-- DROP FUNCTION SP_U_001(typeofarticle varchar);CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR) RETURNS SETOF active_articles AS$BODY$DECLARE myrec RECORD; res active_articles;/**/BEGIN FOR myrec IN select * from articles, articletypes, department where articletypes.articletype_type = $1 AND articles.articletype_id = articletypes.articletype_id AND articles.department_id = department.department_id AND articles.validity_period_end > now() LOOP IF (myrec IS NOT NULL) THEN res.article_type := myrec.articletypes.articletype_type; res.article_author := myrec.articles.author; res.department_owner := myrec.department.department_name ; res.department_picture := myrec.department.department_picture; res.article_title := myrec.articles.title; res.article_content := myrec.articles.content; res.date_creation := myrec.articles.creation_date ; res.date_start := myrec.articles.validity_period_start; res.date_end := myrec.articles.validity_period_end; END IF; RETURN NEXT res; END LOOP; RETURN;END;$BODY$ LANGUAGE 'plpgsql' VOLATILE;ALTER FUNCTION SP_U_001(VARCHAR) OWNER TO immensesk;GRANT EXECUTE ON FUNCTION SP_U_001(VARCHAR) TO immensesk;whatever, i do the argument VARCHAR will be stored in double quote as "varchar" when i check via pgAdmin GUI. Even if in command line it looks like above. I still have the same error message on "myrec" :ERROR: schema "myrec" does not existCONTEXT: SQL statement "SELECT myrec.articletypes.articletype_type"i do not understand as there is quite the same example in postgreSQl 8.1.4 documentation on page 623-624 about "Looping Through Query Results".so where am i wrong ?Al.
Re: [GENERAL] FOR ... IN
Hi William,i've read that RETURN should be used when function does not return a set. in my case, i return a set. so i can not write twice return.Here is my latest version of my function.-- Function: SP_U_001(typeofarticle varchar) -- DROP FUNCTION SP_U_001(typeofarticle varchar);CREATE OR REPLACE FUNCTION SP_U_001(VARCHAR) RETURNS SETOF active_articles AS$BODY$DECLARE myrec RECORD; res active_articles; /**/BEGIN FOR myrec IN select * from articles, articletypes, department where articletypes.articletype_type = $1 AND articles.articletype_id = articletypes.articletype_id AND articles.department_id = department.department_id AND articles.validity_period_end > now() LOOP IF (myrec IS NOT NULL) THEN res.article_type := myrec.articletypes.articletype_type ; res.article_author := myrec.articles.author; res.department_owner := myrec.department.department_name; res.department_picture := myrec.department.department_picture; res.article_title := myrec.articles.title; res.article_content := myrec.articles.content; res.date_creation := myrec.articles.creation_date; res.date_start := myrec.articles.validity_period_start; res.date_end := myrec.articles.validity_period_end; END IF; RETURN NEXT res; END LOOP; RETURN;END;$BODY$ LANGUAGE 'plpgsql' VOLATILE;ALTER FUNCTION SP_U_001(VARCHAR) OWNER TO immensesk; GRANT EXECUTE ON FUNCTION SP_U_001(VARCHAR) TO immensesk;and this is the error message i get :ERROR: schema "myrec" does not existCONTEXT: SQL statement "SELECT myrec.articletypes.articletype_type "PL/pgSQL function "sp_u_001" line 17 at assignmentline 17 consists of WHERE close if you count comments, if not, i consists of last line of my SELECT command ==> AND articles.validity_period_end > now()On 11/7/06, William Leite Araújo <[EMAIL PROTECTED] > wrote:2006/11/7, Alain Roger < [EMAIL PROTECTED]>: but there is already a RETURN NEXT res;so what will be the point of this RETURN after the END LOOP; ? http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html -- William Leite Araújo
[GENERAL] data does not exist :-(
Hi,Finally, i've come to the following solution for my stored procedure :- Function: "SP_U_001"("TypeOfArticle" "varchar")-- DROP FUNCTION "SP_U_001"("TypeOfArticle" "varchar");
CREATE OR REPLACE FUNCTION "SP_U_001"("TypeOfArticle" "varchar") RETURNS SETOF active_articles AS$BODY$DECLARE myrec RECORD; res active_articles;/**/
BEGIN FOR myrec IN select * from articles, articletypes, department where articletypes.articletype_type = $1 AND articles.articletype_id = articletypes.articletype_id
AND articles.department_id = department.department_id AND articles.validity_period_end > now() LOOP IF (myrec IS NOT NULL) THEN res.article_type := myrec.articletypes.articletype_type;
res.article_author := myrec.articles.author; res.department_owner := myrec.department.department_name; res.department_picture := myrec.department.department_picture; res.article_title
:= myrec.articles.title; res.article_content := myrec.articles.content; res.date_creation := myrec.articles.creation_date; res.date_start := myrec.articles.validity_period_start;
res.date_end := myrec.articles.validity_period_end; END IF; RETURN NEXT res; END LOOP;END;$BODY$ LANGUAGE 'plpgsql' VOLATILE;ALTER FUNCTION "SP_U_001"("TypeOfArticle" "varchar") OWNER TO immensesk;
GRANT EXECUTE ON FUNCTION "SP_U_001"("TypeOfArticle" "varchar") TO immensesk;---however, when i run it thanks --> select SP_U_001('action');
i get the following answer : ERROR: function sp_u_001("unknown") does not existin pgAdmin III GUI applicationBut this function exists.So where is my mistake ?thx,Al.
[GENERAL] FOR ... IN
Hi,Before (in version 8.0.1), i did the following thing and it was working well...now (in version 8.1.4) it seems that it does not work anymore...problem is with FOR rec IN loop...So how can i tell "FOR all RECORDS from select * from articles, articletypes, department where ..." LOOP ... ?
thanks,Al.CREATE OR REPLACE FUNCTION "public"."SP_U_001" ("TypeOfArticle" varchar) RETURNS SETOF "public"."active_articles" AS$body$DECLARE
TypeArt VARCHAR := $1; rec RECORD; res active_articles;/**/BEGIN FOR rec IN select * from articles, articletypes, department
where articletypes.articletype_type = $1 AND articles.articletype_id = articletypes.articletype_id AND articles.department_id = department.department_id AND articles.validity_period_end
> now() LOOP res.article_type := rec.articletypes.articletype_type; res.article_author := rec.articles.author; res.department_owner := rec.department.department_name; res.department_picture
:= rec.department.department_picture; res.article_title := rec.articles.title; res.article_content := rec.articles.content; res.date_creation := rec.articles.creation_date; res.date_start
:= rec.articles.validity_period_start; res.date_end := rec.articles.validity_period_end; RETURN NEXT res; END LOOP;END;$body$LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
[GENERAL] stored procedure / Function
Hi,How to retrieve the IN parameter of a function for later use ?i tried this :CREATE OR REPLACE FUNCTION "public"."SP_U_001" ("TypeOfArticle" varchar) RETURNS SETOF "public"."active_articles" AS
$body$DECLARE TypeArt VARCHAR := TypeOfArticle; rec RECORD; res active_articles;/**/BEGIN SELECT articletypes.articletype_id INTO tpart FROM articletypes
WHERE articletypes.articletype_type = TypeArt;but it seems that TypeArt VARCHAR := TypeOfArticle; does not workCan you help me ?thx.AL.
[GENERAL] basic SQL request
Hi,i've tried to run a basic SQL request as followed :select *from articles, articletypes, departmentwhere articles.articletype_id = articletype.articletype_id AND articles.department_id = department.department_id AND articles.validity_period_end > now()and i got the following error message :ERROR: missing FROM-clause entry for table "articletype"i'm confused now, if i use LEFTJOIN it's the same, so where is the trouble ? thx,AL.
[GENERAL] varchar
Hi,I would like to allow web site user to fill a field and for that i would need a large varchar()...maybe something around 100.000 characters.i guess that VARCHAR can not hold so many character and that i should turn to bytea. Am I right or is there some other possibility ?i'm asking that because i've seen that bytea has some issues to store non ASCII characters like from UNICODE andSlovak language...so how can i store central europe characters (UNICODE) into BYTEA field ? thx,Alain
[GENERAL] Issue when inserting Slovak characters in database via PHP code
Hi,Sorry to cross post this mail but i'm not able to know from where comes my issue.I have a postgreSQL database in UNICODE (UTF-8 in v8.1.4 and UNICODE in v8.0.1).Via my web application i type a sentence in Slovak language and it is stored into DB without any slovak characters. Instead of that, all particular characters are replace with \303\251 or \303\206 or \304\314 and so on... I was thinking that issue was coming from DB encryption but on 2 different versions of DB (see above) i get the same result.after, i was thinking that it was coming from my web browser, but even if i setup character mode in central europe and Slovak language as default coding...nothing change...i tried on IE and Firefox. Last step, i tried to type directly from my PhpPgAdmin (direct typing sentence there to DB), and i realize that when i click on save...the changes appear in DB aswritten above (e.g. : \303\251,...)My latest test was to write via PhpPgAdmin (directly to DB) the UNICODE of slovak character contained within my sentence...so i used ý, í and so on... if i do that, those code are correctly saved into DB and when my PHP code show web pages, all sentences are correct.I can not imagine to write a special interface to convert slovak characters to unicode everytime that user would like to type something new. Something else must be badly setup...Please, could you tell me where to search because i'm confused now...thx.Alain
[GENERAL] bytea / large object and image
Hi,I create a table with some large object (ref: OID) to store some images.When my PHP will display some data, it will also display the images stored as OID.However, i've read that before i must restore the image by exporting them to local (on server) file. isn't it easier in this case, to simply store the path and file name of file to DB and just read the data to display image on PHP pages ?what is the purpose in this case to store image a bytea / large object ? thanks a lot,Al.
Re: [GENERAL] UNICODE and UTF-8
Yes, my provider has v8.0.1 and i have installed v8.1.4.thanks for your confirmation.Al.On 11/4/06, Martijn van Oosterhout < kleptog@svana.org> wrote:On Sat, Nov 04, 2006 at 04:23:02PM +0100, Alain Roger wrote: > however, when i do this, my encoding is in UTF-8 via phpAdmin.> UTF8 is a part of UNICODE, but as i'm not sure on how many bits is UNICODE,> how can i setup my local DB to UNICODE value as my provider has ? As far a postgres is concerned, UTF8 is UNICODE. IIRC some versionssaid one name, some the other, but they mean the same thing.So maybe you have a different version than your provider?Have a nice day, --Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/> From each according to his ability. To each according to his ability to litigate. -BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFFTMEVIB7bNG8LQkwRAhH4AJ9PlOoZeP7zQ1VG6/6bT1aqcUTz9gCeKM5UpoP157BzFSv0LUGu8fU1iUc==PJtK-END PGP SIGNATURE-
[GENERAL] UNICODE and UTF-8
Hi,I tried to reproduce the same DB on my local server as my web provider gave me access.On this DB (from provider) i can see via phpAdmin pages that encoding is setup to UNICODE.after reading some documentation, i've seen that i had to create my DB on my local server like that : create database mydatabase with encoding='UNICODE';however, when i do this, my encoding is in UTF-8 via phpAdmin.UTF8 is a part of UNICODE, but as i'm not sure on how many bits is UNICODE, how can i setup my local DB to UNICODE value as my provider has ? thanks a lot,Al.
[GENERAL] PG_DUMP without asking password
Hi,is there a way to backup the database thanks a command script, without postgresql requesting the user password ?thanks a lot,Alain
[GENERAL] Backup roles / users
Hi,I've checked in pg_dump and i did not find anything regarding backuping roles ?When i migrate my DB to another computer, should i recreate all roles manually ?thx.Alain
[GENERAL] Backup / export DB
Hi,I have a database (table, stored procedures, accounts,..) on 1 computer.for some reason i need to move this database to another computer but not in the same folder name or on the same HDD.how can i export (and after import) all relative structure and data ? or maybe a backup of DB is enough ?thanks a lot,Alain
[GENERAL] SELECT statement in stored procedure
Hi, I have the following stored procedure : CREATE OR REPLACE FUNCTION immense_sp001(IN username VARCHAR, IN strhash VARCHAR) RETURNS SETOF accounts LANGUAGE plpgsql AS ' DECLARE Profile_Detected INTEGER :=0; act accounts%ROWTYPE; rec RECORD; BEGIN /* detect if the user logged in exists in database*/ SELECT count(*) INTO Profile_Detected FROM accounts WHERE login=username AND pwd=strhash; if (Profile_Detected = 1) then SELECT INTO act * FROM accounts; FOR rec IN select login,status from accounts LOOP RETURN NEXT rec; END LOOP; end if; return; END; '; so it should return only 2 fields from my account table (login and status). however it does not work. if i replace the line "FOR rec IN select login,status from accounts LOOP" by FOR rec IN select * from accounts LOOP it works but i get all fields from my accounts table. So how can i get only login and status ? thanks a lot, Alain ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] stored procedure which return a select result
Hi,I have some problems with a stored procedure.In this SP, i run several SELECT statements.1st one is to confirm that profile/account really exist into DB.if it's ok, the 2nd statement (SELECT) is executed and should return records. these records should be the result of my SP.i try the RETURNS setof record as ...but it seems not so great...at least i have problems with it...I've search in several books, but i did not find a real example which could help me. Could you give me some piece of code for such use ?Or, if this technique is not used, could you tell me what is used for returning a SELECT result as result of SP ?thanks a lotAlain
[GENERAL] stored procedures
Hi,i'm migrating some SP from MySQL to PostgreSQL 8.1.xI would like to know if it is possible to return aresult of a select request and also a simple interger...thanks a lot,Alain
[GENERAL] phppgadmin
Hi,I'm back on my problem with PHPpgadmin.. :-(when i log in for the first time, the server icon of servers list, is changing to Loadingand it is back on "not connected" server, but i've access to my database normally. each time that i click on some items (create database, accounts, create tables,...), PHPpgadmin asks me again username and pwd.i've checked if my browsers allow cookies and sessions.. Everything is fine... I also downgrade from apache 2.2.2 to apache 2.0.58 just in case of (based on my problem with php5apache2.dll issue in apache 2.2.2)Does anyone have some idea where could be located the problem ?I did the same configration at work that i have a home (from my network). thanks a lotAlain
[GENERAL] phppgadmin
Hi,I installed PHPpgadmin and there is a strange behavior.everytime that i select an object (database, table, schema,..) phppgadmin asks me to enter my login and password.why did it not keep this information from 1st connection ? thanks a lot,Alain
[GENERAL] PHPpgadmin and user privileges
Hi,I would like to provide access to PostgreSQL via PHPpgadmin.basically when user will enter login + password, he will connect directly to his database.what i've done till now, it does not work. when user enter login and password, phppgadmin returns "Login failed".my user hasfor profile "kmt_admin" which belong to "kmt_ad" group role" and which is owner of "kmt" database. Could you tell where to check to avoid such error ?thanks a lot,Alain
[GENERAL] restrict user rights
Hi,I have some little issue with restricting right for a profile.i've created a profile "kmt_admin" that should have only access to "kmt" database.this profile should be able to create table, functions, but it should not have access to other databases or to create additional profiles... i setup everything according documentation and whe i try to connect with PHPpgadmin, the profile kmt_admin can have access to all database as a super user...please, could you help me ?thanks a lot, Alain
