Re: [GENERAL] create a table inside a function

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:

Re: [GENERAL] create a table inside a function

; >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(

[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)

[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

[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

[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. --

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 a

[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 Apac

[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 C

[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

[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

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

Re: [GENERAL] restore a dump db from tar file

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.

[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 coul

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 = t

[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 ti

[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

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

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" <[EM

[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 an

[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 '==' o

Re: [GENERAL] returned value from SQL statement

nl_lang > ); > > DELETE FROM cust_portal.tmp_newsletterreg WHERE > tmp_newsletterreg.email = email; > > COMMIT; > RETURN(0); > > EXCEPTION > ROLLBACK; > RETURN(-2); > > END IF; > END; >

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 > > statem

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 ? > > >

[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

Re: [GENERAL] return value from SQL statement

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 >

[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# 2

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: > Al

Re: [GENERAL] date and time

4, 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

[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 -

[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

[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' ); t

Re: [GENERAL] sequence

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

[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...speciall

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 avai

[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

[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_

[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 the

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 b

[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\\

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 &

[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 ---

[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 correctl

[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$ DECLAR

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 corre

[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 i

[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 us

[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 imme

[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 pro

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

[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

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

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:>

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(typeofart

[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;/

Re: [GENERAL] FOR ... IN

f 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 NE

[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 activ

[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,A

[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;/***

[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 messa

[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

[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, al

[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 s

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

[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 my

[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 ?tha

[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;

[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 set

[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 tabl

[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

[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 setu