Thread: insert more than one rows
Hi,
I'm using Pgsql 8.1 in my project and I have to insert more than one rows with one sql query as the insert commmand slows a lot the server and comsumes too much resources. I have read some older mails about this and most of them recommended to use COPY command but the thing is I don't have a chance to create a file. I have a php form which takes data and then execute some queries after the submit. I will have probably 100 rows maximum (just a guess). It wont slow down a lot, but I want to find and use a better alternative than to use a for statement to insert datas.
In some of the mails someone had talked about to use the copy's stdout command. But I can't honestly say that I understood clearly how to use it.
I don't want to create a file with php and then populate and then use the copy command by that as it will be more problematic. So what do you suggest? Can I use the COPY command without pointing a file? Or can I point a string variable to use it as source while the delimeter is something else than the tab character (the | character perhaps). Or can I point a string array simply? (When i say point i mean to use it as datasource. Sorry for my bad english)
Thank you for your time
Roy Simkes
Be one of the first to try Windows Live Mail beta
am 28.06.2006, um 7:59:44 +0000 mailte roy simkes folgendes: > Hi,I'm using Pgsql 8.1 in my project and I have to insert more than > one rows with one sql query as the insert commmand slows a lot the > server and comsumes too much resources. I have read some older mails > about this and most of them recommended to use COPY command but the > thing is I don't have a chance to create a file. I have a php form Read this: http://people.planetpostgresql.org/greg/index.php?/archives/56-Inserting-multiple-rows-in-a-single-statement.html HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
On 6/28/06, roy simkes <roysimkes@hotmail.com> wrote:
> Can I use the COPY command without pointing a file? Or can I point a string
> variable to use it as source while the delimeter is something else than the
> tab character (the | character perhaps). Or can I point a string array
> simply? (When i say point i mean to use it as datasource. Sorry for my bad
> english)
Another alternative to the suggestion in the link Andreas provided
might be to use the COPY FROM STDIN method;
If you have your query-results in an array of strings, separator a
pipe-symbol, for example, something like the following snippet might
work for you:
PQexec(conn, "CREATE TABLE foo (a int4, b char(16), d double precision)");
PQexec(conn, "COPY foo FROM STDIN DELIMITER AS '|'");
foreach ($my_results as &$value) {
PQputline(conn, $value);
}
PQputline(conn,"\\.\n");
PQendcopy(conn);
Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
http://www.american.edu/econ/notes/htmlmail.htm
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Andrej
Ricnik-Bay
Sent: Wednesday, June 28, 2006 1:34 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] insert more than one rows
On 6/28/06, roy simkes <roysimkes@hotmail.com> wrote:
> Can I use the COPY command without pointing a file? Or can I point a
string
> variable to use it as source while the delimeter is something else
than the
> tab character (the | character perhaps). Or can I point a string array
> simply? (When i say point i mean to use it as datasource. Sorry for my
bad
> english)
Another alternative to the suggestion in the link Andreas provided
might be to use the COPY FROM STDIN method;
If you have your query-results in an array of strings, separator a
pipe-symbol, for example, something like the following snippet might
work for you:
PQexec(conn, "CREATE TABLE foo (a int4, b char(16), d double
precision)");
PQexec(conn, "COPY foo FROM STDIN DELIMITER AS '|'");
foreach ($my_results as &$value) {
PQputline(conn, $value);
}
PQputline(conn,"\\.\n");
PQendcopy(conn);
Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes
concise.
http://www.american.edu/econ/notes/htmlmail.htm
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
-----Original Message-----
Is there a way to do that without using deprecated libpq copy functions?
Thanks,
Josh
On 7/1/06, Garcia, Joshua <Joshua.Garcia@xerox.com> wrote:
>> PQexec(conn, "CREATE TABLE foo (a int4, b char(16), d double
>> precision)");
>> PQexec(conn, "COPY foo FROM STDIN DELIMITER AS '|'");
>> foreach ($my_results as &$value) {
>> PQputline(conn, $value);
>> }
>> PQputline(conn,"\\.\n");
>> PQendcopy(conn);
> Is there a way to do that without using deprecated libpq copy functions?
Ummm ... copy in that example is an SQL function.
http://www.postgresql.org/docs/8.1/interactive/sql-copy.html
Unless of course the PHP guys have made any of the sql statements
an implementation of libpq (in which case the answer to your
question would be no).
> Thanks,
> Josh
Cheers,
Andrej
Is it possible to assign a column name to a variable and then use that
variable in a SELECT statement inside a function using plpgsql?
For example:
CREATE OR REPLACE FUNCTION function_name("varchar")
RETURNS varchar AS
$BODY$
DECLARE
column_name alias for $1;
status varchar;
BEGIN
SELECT column_name INTO status FROM contact_table WHERE id = 1;
RETURN status;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Thanks,
Derrick
am 30.06.2006, um 16:32:38 -0600 mailte Derrick Betts folgendes: > Is it possible to assign a column name to a variable and then use that > variable in a SELECT statement inside a function using plpgsql? Yes. You can define a string with your SQL, including variables. Thena you can EXECUTE this string. http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
Excellent! Thank you so much! I've been searching for this solution for a very long time. Your response is very much appreciated. Derrick ----- Original Message ----- From: "A. Kretschmer" <andreas.kretschmer@schollglas.com> To: <pgsql-novice@postgresql.org> Sent: Sunday, July 02, 2006 11:00 PM Subject: Re: [NOVICE] Column names as variables in plpgsql > am 30.06.2006, um 16:32:38 -0600 mailte Derrick Betts folgendes: >> Is it possible to assign a column name to a variable and then use that >> variable in a SELECT statement inside a function using plpgsql? > > Yes. You can define a string with your SQL, including variables. Thena > you can EXECUTE this string. > > http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > > > HTH, Andreas > -- > Andreas Kretschmer (Kontakt: siehe Header) > Heynitz: 035242/47215, D1: 0160/7141639 > GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net > === Schollglas Unternehmensgruppe === > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > >