[GENERAL] Bulk data insertion
Hello, I have a PL/PgSQL function that I need to call with some ARRAY parameters. These array values are very large -- typically thousands of elements. Each element is a 4-element array. This function is called to do some sanity checking on the array data and use the individual elements to do inserts where appropriate. The problem is that I don't want to spend a lot of time and memory building such a query (in C). I would like to know if there is a way to take this huge chunk of data and get it into the database in a less memory-intensive way. I suppose I could use COPY to put the data into a table with triggers that would do the checks on the data, but it seems inelegant and I'd like to know if there's a better way. Thoughts? Thanks for your time. -- Jonathan Daugherty Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] User-defined types
# > "Whenever a user-defined base data type is created, PostgreSQL # > automatically creates an associated array type, whose name consists # > of the base type's name prepended with an underscore. # # The key word in that sentence is "base" data type --- ie, not # composite. Ah, I see -- it wasn't obvious to me. # We don't currently support arrays of composite types. (Nor arrays # of domain types, either, IIRC.) Ok, thanks for your time. -- Jonathan Daugherty Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] User-defined types
Hello, I'm trying to write a PL/PgSQL function whose sole parameter is an array whose element type is a type that I've created. For example: CREATE TYPE test_type AS (x bigint, y bigint); CREATE OR REPLACE FUNCTION array_test (test_type[]) ... According to the 7.4 docs, "Whenever a user-defined base data type is created, PostgreSQL automatically creates an associated array type, whose name consists of the base type's name prepended with an underscore. The parser understands this naming convention, and translates requests for columns of type foo[] into requests for type _foo. The implicitly-created array type is variable length and uses the built-in input and output functions array_in and array_out." However, mydb=# SELECT COUNT(*) FROM pg_type WHERE typname = '_test_type'; count --- 0 (1 row) Am I missing something? Thanks for your time. I'm running PostgreSQL 7.4.5. -- Jonathan Daugherty Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] determine sequence name for a serial
# But I didn't understand why you care to get rid of the explicit reference to # the sequence object in your code in the first place. In PostgreSQL, at # least for the past 5 years if not longer, if you create a SERIAL column for # (schemaname, tablename, columnname), then your sequence will *always* be # "schemaname.tablename_columnname_seq". If that naming convention changes, # there will be a whole lotta breakage world-wide. When a table is renamed, related sequences' names don't change (as of 7.4.5). The ability to automagically pull the sequence based on the schema.table.column would be nice if you don't want to worry about having to update your table name and sequence name references in code. -- Jonathan Daugherty Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] determine sequence name for a serial
# CREATE OR REPLACE FUNCTION get_default_value (text, text, text) RETURNS text AS ' # SELECT adsrc # FROM pg_attrdef, pg_class, pg_namespace, pg_attribute # WHERE # adrelid = pg_class.oid AND # pg_class.relnamespace = pg_namespace.oid AND # pg_attribute.attnum = pg_attrdef.adnum AND # pg_attribute.attrelid = pg_class.oid AND # pg_namespace.nspname = $1 AND # pg_class.relname = $2 AND # pg_attribute.attname = $3; # ' language sql; As per Tom's mention of pg_depend, here's something that seems to do the trick for the time being, assuming the column is a serial: -- get_sequence(schema_name, table_name, column_name) CREATE OR REPLACE FUNCTION get_sequence (text, text, text) RETURNS text AS ' SELECT seq.relname::text FROM pg_class src, pg_class seq, pg_namespace, pg_attribute, pg_depend WHERE pg_depend.refobjsubid = pg_attribute.attnum AND pg_depend.refobjid = src.oid AND seq.oid = pg_depend.objid AND src.relnamespace = pg_namespace.oid AND pg_attribute.attrelid = src.oid AND pg_namespace.nspname = $1 AND src.relname = $2 AND pg_attribute.attname = $3; ' language sql; -- Jonathan Daugherty http://www.cprogrammer.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] determine sequence name for a serial
# I figured out how to get this:
#
# foo=> SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
# pg_class WHERE relname = 'foo');
#adsrc
#
# nextval('public.foo_id_seq'::text)
# (1 row)
#
# However, this will break as soon as I do this:
#
# foo=> CREATE SCHEMA x;
# CREATE SCHEMA
# foo=> CREATE TABLE x.foo (id SERIAL PRIMARY KEY NOT NULL, x TEXT);
# NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for
# "serial" column "foo.id"
# NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
# "foo_pkey" for table "foo"
# CREATE TABLE
# foo=> SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
# pg_class WHERE relname = 'foo');
# ERROR: more than one row returned by a subquery used as an
# expression
This should suffice to get you a string you can regex. Other than the
default value setting for the serial, I don't see another link that
binds the serial to its sequence.
CREATE OR REPLACE FUNCTION get_default_value (text, text, text) RETURNS text AS '
SELECT adsrc
FROM pg_attrdef, pg_class, pg_namespace, pg_attribute
WHERE
adrelid = pg_class.oid AND
pg_class.relnamespace = pg_namespace.oid AND
pg_attribute.attnum = pg_attrdef.adnum AND
pg_attribute.attrelid = pg_class.oid AND
pg_namespace.nspname = $1 AND
pg_class.relname = $2 AND
pg_attribute.attname = $3;
' language sql;
--
Jonathan Daugherty
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, (503) 667-4564
---(end of broadcast)---
TIP 8: explain analyze is your friend
