error cannot cast type text to regclass
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn regclass postgres more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags postgresql nextval example Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like
Nextval Postgresql
you, helping each other. Join them; it only takes a minute: Sign up What does regclass signify in Postgresql up vote 15 down vote favorite 3 I have the following line in a CREATE TABLE statement: field1_id bigintPostgresql Cast
DEFAULT nextval('table1_field1_id_seq'::regclass) NOT NULL, What does regclass signify in the above? Is it an absolute requirement to add ::regclass? N.B: I had seen the Postgresql documentation link which tells about regclass, but couldn't understand it. postgresql create-table share|improve this question edited Nov 9 '12 at 4:21 Craig Ringer 134k18222304 asked Nov 8 '12 at 12:26 saji89 83511232 +1 linked to the documentation you're talking about, thanks. –Craig Ringer Nov 9 '12 at 4:22 add postgres oid a comment| 2 Answers 2 active oldest votes up vote 25 down vote accepted No, you do not need the cast to regclass when calling a function like nextval that accepts a regclass parameter, as there is an implict cast from text to regclass. In some other contexts an explicit cast to regclass may be required. Explanation: ::regclass is a cast, like ::integer. regclass is a "magic" data type; it's actually an alias for oid, or "object identifier". See Object identifier types in the documentation. Casting to regclass is a shortcut way of saying "this the name of a relation, please convert it to the oid of that relation". Casts to regclass are aware of the search_path, unlike querying pg_class for a relation's oid directly, so casting to regclass isn't exactly equivalent to subquerying pg_class. Tables are relations. So are sequences, and views. So you can get the oid of a view or sequence by casting to regclass too. There are implicit casts defined for text to regclass, so if you omit the explicit cast and you're calling a function that accepts regclass the cast is done automatically. So you do not need it in, for example, nextval calls. There are other places where you may. For example you can't compare text directly with oid; so you can do this: regress=> select * frompgsql-announce pgsql-bugs pgsql-docs pgsql-general pgsql-interfaces pgsql-jobs pgsql-novice pgsql-performance pgsql-php pgsql-sql pgsql-students Developer lists Regional lists Associations
Pg_class
User groups Project lists Inactive lists IRC Local User postgres cast to string Groups Featured Users International Sites Propaganda Resources Weekly News restoring db from 8.1 in create sequence postgres a 8.0 db From: sten govaertslog in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about http://dba.stackexchange.com/questions/52835/automatic-conversion-of-anonymous-records-returned-from-an-udf-to-well-known-tab hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Automatic conversion of anonymous error cannot records returned from an UDF to well known table-type up vote 2 down vote favorite Proprietary code (that we cannot change) has a bunch of user defined functions of the type: create or replace function f() returns record as $$ ... $$ which we call in the following way (for example): SELECT status, log FROM f() as (status boolean, log text); (status boolean, log text) is a rowtype of table T. Is it possible to automatically convert the record error cannot cast (or setof record return type) into the T rowtype without listing the attributes? What I am looking for is of the kind: SELECT * FROM f() as T%rowtype postgresql plpgsql type-conversion functions composite-types share|improve this question edited Nov 6 '13 at 16:30 Erwin Brandstetter 53.1k584142 asked Nov 5 '13 at 17:13 arthur 271412 1 If the function does not have arguments, you could wrap that into a view. –a_horse_with_no_name Nov 5 '13 at 17:26 1 Or you could just as well wrap these into functions in an other schema but an identical signature. Or if you know the source, create your own, usable set of them. –dezso Nov 5 '13 at 21:28 Technically, I think, what you suggest is not possible. –dezso Nov 5 '13 at 21:35 @dezso: I found a way for functions returning a single row. –Erwin Brandstetter Nov 6 '13 at 16:31 @ErwinBrandstetter Yes, and it is as simple as it can be, but still a wrapping :). Assignment to and from record/row variables are full of surprises by the way. –dezso Nov 6 '13 at 21:06 | show 1 more comment 1 Answer 1 active oldest votes up vote 4 down vote accepted There is a way. Given a table t and a function f() that returns an anonymous record that would match that table type: CREATE TABLE t (id int, d date); You canno