Обсуждение: Simple Insert Function Syntax
Hello,
Does anyone have an example of a simple insert function. I am new to
PostGreSQL and am having a little trouble with the basics. There is no
simple (to me) example in the book... Here is what I am trying to do:
CREATE FUNCTION sp_add_dealerinfo(Text, Text, Text, Int, Text, Text,
Text, Text, Text, Text, Float4,
Float4, Varchar(1024))
AS
'Insert Into (ID, Name, StoreName, Address, NumberOfLocations, ContactName,
Email, HomePhone, WorkPhone, BestTime, Products, TotalAnnualSales,
TotalCreditSales, Comments)
Values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13);'
Language 'sql';
If anyone can help, a simple one column insert function will do. I prefer
the encapsulation and speed that is provided by putting queries into db
functions instead of inline in my PHP or other language... so this is
important to me. I am still struggling with PGSQL syntax.
I come from an MSSQL/Oracle background... but am not willing to spend the
outrageous licensing for either of these software packages for my own
projects... it is just not worth it for the product that you get!
thx,
Neil P Davis
_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
Share information about yourself, create your own public profile at
http://profiles.msn.com.
Does anyone know of a utility which can easily convert MS Access tables into Postgres? Alternatively, I can export to any format that MSAccess supports (e.g. comma-delimited text), but would like to know if there are any import utilities which will simplify the import process into postgres so I don't have to write a script for 100 fields X thousands of records. Thanks for your help! Kevin
On Thu, 12 Oct 2000, Kevin Leclaire wrote: > Does anyone know of a utility which can easily convert MS Access tables into > Postgres? pgAdmin has an Access migration utility. It works through the Pg ODBC driver. See http://www.pgadmin.freeserve.co.uk/ > Alternatively, I can export to any format that MSAccess supports (e.g. > comma-delimited text), but would like to know if there are any import > utilities which will simplify the import process into postgres so I don't > have to write a script for 100 fields X thousands of records. If you export into some kind of delimited format, you can easily use the COPY or /copy commands with psql. Brett W. McCoy http://www.chapelperilous.net --------------------------------------------------------------------------- The full potentialities of human fury cannot be reached until a friend of both parties tactfully interferes. -- G.K. Chesterton
Kevin, Ref : Thursday, October 12, 2000 11:05:10 PM KL> Does anyone know of a utility which can easily convert MS Access tables into KL> Postgres? KL> Alternatively, I can export to any format that MSAccess supports (e.g. KL> comma-delimited text), but would like to know if there are any import KL> utilities which will simplify the import process into postgres so I don't KL> have to write a script for 100 fields X thousands of records. KL> Thanks for your help! KL> Kevin RTFM ! 1) Create a table in postgres create table foo ( name text, phone text, age integer ); 2) export your table in "tab" delimited format from access with the fields in the same order as in postgres : john 5551212 25 paul 5551313 26 georges 5551414 30 ringo 5551515 27 3) use "copy table from..." command copy foo from myaccessexportfile.txt; Look for "COPY" in the docs. -- Jean-Christophe Boggio cat@thefreecat.org Independant Consultant and Developer Delphi, Linux, Oracle, Perl
> Kevin, > > Ref : Thursday, October 12, 2000 11:05:10 PM > > KL> Does anyone know of a utility which can easily convert MS Access tables into > KL> Postgres? > > KL> Alternatively, I can export to any format that MSAccess supports (e.g. > KL> comma-delimited text), but would like to know if there are any import > KL> utilities which will simplify the import process into postgres so I don't > KL> have to write a script for 100 fields X thousands of records. > > KL> Thanks for your help! > KL> Kevin > > > RTFM ! > > 1) Create a table in postgres > create table foo > ( > name text, > phone text, > age integer > ); > > 2) export your table in "tab" delimited format from access with the > fields in the same order as in postgres : > john 5551212 25 > paul 5551313 26 > georges 5551414 30 > ringo 5551515 27 > > 3) use "copy table from..." command > copy foo from myaccessexportfile.txt; > this is such a manual process it stinks. i too asked recently for a nice loading tool. the advice i got was to hack togethersome perl scripts. although this would probably work fine, i think there is justification to build a real utility. i'm currently working on one which can convert data from one format to another. any database uploading or download willbe done through ODBC. formats support will be TEXT(DELIMITED, FIXED_WIDTH, ENCLOSED), XBASE, and of course any SQL DB through ODBC. utilizing the tool will entail the preparation of a specification file, where the input file or data table or data queryis specified and the output file or data table is also specified, along with column positions/names, field delimiters, etc. the idea is to approach the functionality of oracle's sqlload tool, while making it more flexible through ODBC. although it will only suit my immediate needs and not be extremely robust, when its finished, i will probably release itto the contrib directory. the COPY command is the sorriest excuse for a data load tool i have ever had the displeasure of working with. i hope i neverhave to again. tonys.
Tony, Ref : Friday, October 13, 2000 12:12:59 AM TS> this is such a manual process it stinks. i too asked recently for a nice loading tool. the advice i got was to hacktogether some TS> perl scripts. although this would probably work fine, i think there is justification to build a real utility. Well, I'm really sorry, I LOVE the copy command. If some tool does exist to transfer automatically data between databases, this is very good but I don't think I will ever user it. I like to know what happens when I work. I don't believe in magic (I would use MySQL if I did). I like typing, I like coding, I like using pipes, I like command lines and I like perl. I like speed and reliability. TS> i'm currently working on one which can convert data from one format to another. any database uploading or download willbe done TS> through ODBC. formats support will be TEXT(DELIMITED, FIXED_WIDTH, ENCLOSED), XBASE, and of course any SQL DB throughODBC. Sounds cool ! Any release date ? TS> utilizing the tool will entail the preparation of a specification file, where the input file or data table or data queryis TS> specified and the output file or data table is also specified, along with column positions/names, field delimiters, etc. the idea TS> is to approach the functionality of oracle's sqlload tool, while making it more flexible through ODBC. TS> although it will only suit my immediate needs and not be extremely robust, when its finished, i will probably releaseit to the TS> contrib directory. great TS> the COPY command is the sorriest excuse for a data load tool i have ever had the displeasure of working with. i hopei never have TS> to again. * Let's do it again : 1. create the table in pg (access will surely generate SQL for that) 2. have a tab delimited ascii file with fields ordered (access can do that too ?) 3. type ONE very easy command With a little bit of organization it is very simple * It is fully scriptable (so that imports/exports can be done during the night, automatically) * There's nothing to install or configure or learn * It does not depend on a compiler, or a special tool to work straight out of the box * It's very reliable I was very pleased to see how imports/exports were simple in Postgres and I remembered what a pain it had been with Oracle. Regards, -- Jean-Christophe Boggio cat@thefreecat.org Independant Consultant and Developer Delphi, Linux, Oracle, Perl
TS> the COPY command is the sorriest excuse for a data load tool i have
TS> ever had the displeasure of working with.
COPY was never intended as a data format conversion tool. It started out
as a quick & dirty tool for saving and restoring database contents.
It's accreted a few frammishes over the years, notably the user-settable
delimiter option, but basically it's still designed to read files that it
wrote itself.
I agree with Tony that a separate tool for format conversions would be
mighty handy. I don't think that trying to load down COPY with more
features is an appropriate approach --- let it do what it does well,
namely database backup and restore. But we do need something that can
handle the layout and data-conversion tasks needed to import data from
other DBMSes.
regards, tom lane
all, > TS> the COPY command is the sorriest excuse for a data load tool i have > TS> ever had the displeasure of working with. sorry to be so sarcastic, i'm having a bad day. the main point is along the lines, of what tom has stated below. > COPY was never intended as a data format conversion tool. It started out > as a quick & dirty tool for saving and restoring database contents. > It's accreted a few frammishes over the years, notably the user-settable > delimiter option, but basically it's still designed to read files that it > wrote itself. > I agree with Tony that a separate tool for format conversions would be > mighty handy. I don't think that trying to load down COPY with more > features is an appropriate approach --- let it do what it does well, > namely database backup and restore. But we do need something that can > handle the layout and data-conversion tasks needed to import data from > other DBMSes. > > regards, tom lane > my hope is that using ODBC will be a good first crack at this. also, i'm interested in loading other kinds of "free format"text files that are too complex for the COPY command to handle. my hope is that this tool can be the basis for something comprehensive in this department, possible even of use to other database users. now only one problem: if i write this little tool and pop it into contrib, everyone will know how badly i code ... tonys.
On Thu, 12 Oct 2000, Tom Lane wrote:
> I agree with Tony that a separate tool for format conversions would be
> mighty handy. I don't think that trying to load down COPY with more
> features is an appropriate approach --- let it do what it does well,
> namely database backup and restore. But we do need something that can
> handle the layout and data-conversion tasks needed to import data from
> other DBMSes.
How about a PostgreSQL equivalent of something like Oracle's SQL*Loader?
You still have to create control files and do some manual stuff, but it is
a very powerful utility.
Brett W. McCoy
http://www.chapelperilous.net
---------------------------------------------------------------------------
I always wake up at the crack of ice.
-- Joe E. Lewis