Thread: inserting values into types
Hi, ive generated a user defined type: CREATE TYPE qwerty_UDT AS (abc INT); & table as: CREATE TABLE t (col1 qwerty_UDT); my prob is that when i try to insert into the type i.e: INSERT INTO t (col1) Values (qwerty_UDT(123)); i get the error: ERROR: function test_x(integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. which is quite confusing, does anyone have any ideas or exp with this scenario, or offer help in any way? ty in advance :) -- ______________________________________________ Check out the latest SMS services @ http://www.linuxmail.org This allows you to send and receive SMS through your mailbox. Powered by Outblaze
On Wed, Dec 01, 2004 at 07:51:17AM +0800, Andrew Thorley wrote: > ive generated a user defined type: CREATE TYPE qwerty_UDT AS (abc INT); > > & table as: CREATE TABLE t (col1 qwerty_UDT); Are you using 8.0? I don't think earlier versions allowed this. > my prob is that when i try to insert into the type i.e: INSERT INTO t (col1) Values (qwerty_UDT(123)); > > i get the error: > > ERROR: function test_x(integer) does not exist This error doesn't agree with the INSERT statement you gave -- it should say "function qwerty_udt(integer) does not exist". Where does test_x() come from? Did you type the SQL statements and/or error messages instead of cutting and pasting? We can see what's happening, but it's better to paste the exact statements and output to avoid mistakes. > HINT: No function matches the given name and argument types. You may need to add explicit type casts. See the "Composite Types" documentation -- it has a section entitled "Composite Value Input": http://developer.postgresql.org/docs/postgres/rowtypes.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Wed, Dec 01, 2004 at 06:07:34PM +0800, Andrew Thorley wrote: > > Did you type the SQL statements and/or error messages instead > > of cutting and pasting? > > yes i C&P'ed the SQL code & error code. But did you copy the error message associated with the SQL code you copied, or did you copy some other error message? I ask because the function name in the error doesn't match the function name in the INSERT statement. Here's what I get when I execute the statements in your message: test=> CREATE TYPE qwerty_UDT AS (abc INT); CREATE TYPE test=> CREATE TABLE t (col1 qwerty_UDT); CREATE TABLE test=> INSERT INTO t (col1) Values (qwerty_UDT(123)); ERROR: function qwerty_udt(integer) does not exist You said the error you got was: ERROR: function test_x(integer) does not exist The error is the same but the detail differs: your error refers to test_x(integer) instead of qwerty_udt(integer). So where is test_x coming from? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
hi mike, sorry for confusion, the test_x is my mistake, its from another type i created which is executing the same code. my error i get from: CREATE TYPE qwerty_UDT AS (abc INT); CREATE TABLE t (col1 qwerty_UDT); INSERT INTO t (col1) VALUES (qwerty_UDT(123)); is: ERROR: function qwerty_udt(integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. ----- Original Message ----- From: "Michael Fuhr" <mike@fuhr.org> To: "Andrew Thorley" <andrew.thorley@linuxmail.org> Subject: Re: [SQL] inserting values into types Date: Wed, 1 Dec 2004 10:53:01 -0700 > > On Wed, Dec 01, 2004 at 06:07:34PM +0800, Andrew Thorley wrote: > > > > Did you type the SQL statements and/or error messages instead > > > of cutting and pasting? > > > > yes i C&P'ed the SQL code & error code. > > But did you copy the error message associated with the SQL code you > copied, or did you copy some other error message? I ask because > the function name in the error doesn't match the function name in > the INSERT statement. Here's what I get when I execute the statements > in your message: > > test=> CREATE TYPE qwerty_UDT AS (abc INT); > CREATE TYPE > test=> CREATE TABLE t (col1 qwerty_UDT); > CREATE TABLE > test=> INSERT INTO t (col1) Values (qwerty_UDT(123)); > ERROR: function qwerty_udt(integer) does not exist > > You said the error you got was: > > ERROR: function test_x(integer) does not exist > > The error is the same but the detail differs: your error refers > to test_x(integer) instead of qwerty_udt(integer). So where is > test_x coming from? > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ -- ______________________________________________ Check out the latest SMS services @ http://www.linuxmail.org This allows you to send and receive SMS through your mailbox. Powered by Outblaze
"Andrew Thorley" <andrew.thorley@linuxmail.org> writes:
> CREATE TYPE qwerty_UDT AS (abc INT);
> CREATE TABLE t (col1 qwerty_UDT);
> INSERT INTO t (col1) VALUES (qwerty_UDT(123));
> ERROR: function qwerty_udt(integer) does not exist
Just say
INSERT INTO t (col1) VALUES (ROW(123));
Note this will not work at all on pre-8.0 Postgres.
regards, tom lane
> CREATE TYPE qwerty_UDT AS (abc INT); > > CREATE TABLE t (col1 qwerty_UDT); > > INSERT INTO t (col1) VALUES (qwerty_UDT(123)); > > ERROR: function qwerty_udt(integer) does not exist > HINT: No function matches the given name and argument types. You may need to add explicit type casts. Well, doesn't the error message say it ? "function ... does not exist". The question would be why are you doing "qwerty_UDT(123)" in the first place ? It seems you'd want to be casting ? PostgreSQL, in any case, thinks you want to call a function qwerty_UDT(integer) which it can't find. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>> CREATE TYPE qwerty_UDT AS (abc INT); >> >> CREATE TABLE t (col1 qwerty_UDT); >> >> INSERT INTO t (col1) VALUES (qwerty_UDT(123)); >> >> ERROR: function qwerty_udt(integer) does not exist >> HINT: No function matches the given name and argument types. You may need to add explicit type casts. It seems as though you want to create your own user-defined types. That isn't easy in PostgreSQL. You have to create an external C struct and write a function to convert a PostgreSQL string to a C struct and return it to the database (called an input function), and create another function that takes an object from the database and returns a string to the database in order to print out the object (called an output function). The only thing PostgreSQL knows about the object is size of the object and the input and output functions. You also need to be the database administrator. Go here for more information: http://www.postgresql.org/docs/current/static/xtypes.html I had to create an object-oriented database for one of my classes, and although I wanted to use PostgreSQL, I didn't want to deal with so low level stuff. Oracle is much better for object-oriented features. Regards, Yasir
On Sat, Dec 04, 2004 at 11:13:29PM -0500, Yasir Malik wrote: > >>CREATE TYPE qwerty_UDT AS (abc INT); > >> > >>CREATE TABLE t (col1 qwerty_UDT); > >> > >>INSERT INTO t (col1) VALUES (qwerty_UDT(123)); > >> > >>ERROR: function qwerty_udt(integer) does not exist > >>HINT: No function matches the given name and argument types. You may > >>need to add explicit type casts. > > It seems as though you want to create your own user-defined types. That > isn't easy in PostgreSQL. That the CREATE TABLE statement succeeded implies that he's using PostgreSQL 8.0, which has better support for composite types than previous versions. What he's trying to do will work -- he just has the syntax wrong. http://developer.postgresql.org/docs/postgres/rowtypes.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Hi co-fanatics. I am working on a small prove of concept, and am running into a small obstacle. (the prove of concept showing, amongs other things, that doing calculations on a database works well, and that it is possible to let it run 'jobs') Considder the following stored procedure: For reasons of size the complete code is located on my site: http://www.vankoperen.nl/concepts/postgresql/primer/index.html It generates prime numbers for (parameter) odd numbers starting from the biggest known prime in the primes table. The "controller" table makes it possible to abort execution, something wich can be handy if you did a 'select primer(10000000);' I am just getting to grips with the read cashing and the way to circumvent it (using the EXECUTE function) so as to read data each time and thus react to the newest data, especialy the data in the "controller" table in this case. Now what does not seem to work is the opposite thing: i can not, from the console etc, read the new data as the function is generating it. If i 'SELECT count(*);' at the start, or near the end of the running function, it always returns the same. Only when the function is finished it commits and the external select returns the new and correct value. To monitor the function's progress (and for other reasons too, wich are not important in this concept yet) i realy want to read either the UNCOMMITTED data. Or some way to COMMIT it during the functions execution, but currently only the whole function can be regarded as a transaction, and nested transactions is not (yet) supported). Some digging in the mailinglist archives pointed to isolation levels. Apparently 'dirty reads' in theory: [quote http://archives.postgresql.org/pgsql-hackers/2004-08/msg01417.php ] It's only allowed when the transaction is in READ UNCOMMITTED isolation level. Something Postgres doesn't currently support. In fact I'm not aware of any SQL database that supports it, though I'm sure there's one somewhere. You wouldn't normally want to use such a thing, but it could be useful for, for example, seeing what progress a transaction has made for a UI progress meter. [/quote] But not possible for real at the moment? So, summarising: - Nested transactions is not (yet) supported - READ UNCOMMITTED isolation level is not (yet) supported - EXECUTE does not circumvent the transaction Is there a way around this? Regards, Ellert.
You can always use contribs/dblink, or a plperlu/plpythonu function which writes to a file... > So, summarising: > - Nested transactions is not (yet) supported > - READ UNCOMMITTED isolation level is not (yet) supported > - EXECUTE does not circumvent the transaction > > Is there a way around this? > > Regards, > Ellert. > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >