0
CREATE FUNCTION Test_Extract_Text(tm_id1 int4, tm_subid1 int4, tm_id2 int4, tm_subid2 int4)
RETURNS Table 
AS $$
BEGIN
    RETURN QUERY SELECT * FROM dat_extract_text inner join dat_replace_text on dat_extract_text.Id=dat_replace_text.subid ;
END;
$$ LANGUAGE plpgsql;

ERROR:  syntax error at or near "AS"
LINE 3: AS $$

Why? How to fix it?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
D T
  • 3,522
  • 7
  • 45
  • 89

1 Answers1

1

You need to provide a column definition list with the RETURNS TABLE clause, like described in the manual here:

CREATE FUNCTION Test_Extract_Text(
   tm_id1 int4, tm_subid1 int4, tm_id2 int4, tm_subid2 int4)
RETURNS TABLE (col1 type1, col2 type2, ...) AS
$$
BEGIN
   RETURN QUERY
   SELECT *
   FROM   dat_extract_text e
   JOIN   dat_replace_text r ON e.Id = r.subid;
END
$$ LANGUAGE plpgsql;

And while it's allowed to use SELECT *, you should provide a column list here, too. Else, every change to one of the involved tables will break the function.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • but i have very lot columns ( 500 columns) – D T Jul 19 '13 at 09:45
  • @user1497597: That's a lot of columns. Raises the question whether your database schema might be improved. If you need the same table type in multiple functions, you could create a type and use `RETURNS SETOF my_composite_type` ... – Erwin Brandstetter Jul 19 '13 at 09:48
  • Do you have ways differ with it? – D T Jul 19 '13 at 09:55
  • @user1497597: I don't understand that last question, sorry. – Erwin Brandstetter Jul 19 '13 at 10:02
  • 1
    @user1497597 Woah. If you have 500 columns, you almost certainly need to fix your table design... you're going to have major performance problems, and if you keep adding columns you'll hit a limit on the number of columns. See http://stackoverflow.com/q/12606842/398670, https://en.wikipedia.org/wiki/Database_normalization . If you really *must* do this then `CREATE TYPE mytype(...)` and `RETURNS SETOF mytype` will be the only vaguely practical way to go. – Craig Ringer Jul 19 '13 at 12:21