Error Column Row Has Pseudo-type Record
Contents |
8.2 / 8.3 / 8.4 / 9.0 PostgreSQL 8.2.23 Documentation Prev Fast Backward Chapter 8. Data Types Fast Forward Next 8.13. Pseudo-Types The PostgreSQL type system contains a number of special-purpose entries that are collectively postgresql create table from select called pseudo-types. A pseudo-type cannot be used as a column data type, but it can be
Postgres Create Table From Another Table
used to declare a function's argument or result type. Each of the available pseudo-types is useful in situations where a function's behavior does
Postgresql Insert Into Select
not correspond to simply taking or returning a value of a specific SQL data type. Table 8-20 lists the existing pseudo-types. Table 8-20. Pseudo-Types Name Description any Indicates that a function accepts any input data type whatever. anyarray
Postgres Create Table Like
Indicates that a function accepts any array data type (see Section 33.2.5). anyelement Indicates that a function accepts any data type (see Section 33.2.5). cstring Indicates that a function accepts or returns a null-terminated C string. internal Indicates that a function accepts or returns a server-internal data type. language_handler A procedural language call handler is declared to return language_handler. record Identifies a function returning an unspecified row type. trigger A trigger function is declared to return postgres copy table trigger. void Indicates that a function returns no value. opaque An obsolete type name that formerly served all the above purposes. Functions coded in C (whether built-in or dynamically loaded) may be declared to accept or return any of these pseudo data types. It is up to the function author to ensure that the function will behave safely when a pseudo-type is used as an argument type. Functions coded in procedural languages may use pseudo-types only as allowed by their implementation languages. At present the procedural languages all forbid use of a pseudo-type as argument type, and allow only void and record as a result type (plus trigger when the function is used as a trigger). Some also support polymorphic functions using the types anyarray and anyelement. The internal pseudo-type is used to declare functions that are meant only to be called internally by the database system, and not by direct invocation in a SQL query. If a function has at least one internal-type argument then it cannot be called from SQL. To preserve the type safety of this restriction it is important to follow this coding rule: do not create any function that is declared to return internal unless it has at least one internal argument. Prev Home Next Object Identifier Types Up XML Document Support Submit correction If you see anything in the documentation that is
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and select into postgres policies of this site About Us Learn more about Stack Overflow the distinct on company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags postgres create index Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes https://www.postgresql.org/docs/8.2/static/datatype-pseudo.html a minute: Sign up Including a set of rows in a view column up vote 1 down vote favorite Design: A main table where each entry in it can have zero of more of a set of options “checked”. It seems to me that it would be easier to maintain (adding/removing options) if the options were part of a separate table http://stackoverflow.com/questions/7097126/including-a-set-of-rows-in-a-view-column and a mapping was made between the main table and an options table. Goal: A view that contains the information from the main table, as well as all options to which that row has been mapped. However the latter information exists in the view, it should be possible to extract the option’s ID and its description easily. The implementation below is specific to PostgreSQL, but any paradigm that works across databases is of interest. The select statement that does what I want is: WITH tmp AS ( SELECT tmap.MainID AS MainID, array_agg(temp_options) AS options FROM tstng.tmap INNER JOIN (SELECT id, description FROM tstng.toptions ORDER BY description ASC) AS temp_options ON tmap.OptionID = temp_options.id GROUP BY tmap.MainID ) SELECT tmain.id, tmain.contentcolumns, tmp.options FROM tstng.tmain INNER JOIN tmp ON tmain.id = tmp.MainID; However, attempting to create a view from this select statement generates an error: column "options" has pseudo-type record[] The solution that I’ve found is to cast the array of options (record[]) to a text array (text[][]); however, I’m interested in knowing if there is a better solution out there. For reference
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 http://stackoverflow.com/questions/19126148/pl-pgsql-return-query-gives-psuedo-type-record-rather-than-table Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up PL/pgSQL Return Query gives psuedo-type record rather than table up vote 0 down vote favorite My first PL/pgSQL function is designed to take create table a geometry column as input and output a fishnet/grid table (a common task for me). I'm not sure what I'm doing wrong on the return statement, though, as I'm getting back a pseudo-type record instead of a table with distinct columns. My code: CREATE OR REPLACE FUNCTION ST_Fishnet( nrow integer, ncol integer, geo_table varchar, geom_column varchar) RETURNS Table( ids text, geom geometry) AS $$ DECLARE collected_geom geometry; xmin numeric; xmax numeric; ymin numeric; ymax numeric; create table from xsize numeric; ysize numeric; BEGIN EXECUTE 'SELECT st_collect("'||$4||'") FROM "'||$3||'"' INTO collected_geom; xmax := st_xmin(collected_geom); xmin := st_xmax(collected_geom); ymin := st_ymin(collected_geom); ymax := st_ymax(collected_geom); xsize := abs(xmax - xmin) / $2; ysize := abs(ymax - ymin) / $1; RETURN QUERY SELECT i+1||'-'||j+1 as ids, ST_Translate(cell, j * xsize + xmax, i * ysize + ymin) AS geom FROM generate_series(0, $1 - 1) AS i, generate_series(0, $2 - 1) AS j, (SELECT ('POLYGON((0 0, 0 '||ysize||', '||xsize||' '||ysize||', '||xsize||' 0,0 0))')::geometry AS cell) AS temp; END $$ LANGUAGE plpgsql; The output should look like a table with ids and geom columns, but comes out, instead as a pseudo type looking like: (1-1,01030000[...]40) Have read through the docs, and I'm still unsure what I'm doing wrong. Appreciate any pointers. Thanks. postgresql postgis plpgsql share|improve this question edited Oct 1 '13 at 21:54 asked Oct 1 '13 at 21:20 JMcClure 78210 possible duplicate of RETURN QUERY-Record in PostgreSQL –Erwin Brandstetter Oct 2 '13 at 0:23 Don't cross-post: gis.stackexchange.com/questions/73076/… –Mike T Oct 2 '13 at 2:40 add a comment| 1 Answer 1 active oldest votes up vote 2 down vote accepted Figured it out. Calling the function SELECT * FROM ST_Fishnet(rows, columns, geo-table, the_geom)produced the desired table. share|improve this answer answered Oct 1 '13 at 22:00 JMcClure 78210 add a comment| You