0

This obviously doesn't work:

SELECT regexp_matches[1], regexp_matches[1]
FROM ROWS FROM (
  regexp_matches('fooBarBaz', '[[:upper:]]', 'g'),
  regexp_matches('fooBarBaz', '[[:lower:]]', 'g')
);

Error: [42702] column reference "regexp_matches" is ambiguous

How can I give an alias to the function calls?

SELECT u[1], l[1]
FROM ROWS FROM (
  regexp_matches('fooBarBaz', '[[:upper:]]', 'g'),
  regexp_matches('fooBarBaz', '[[:lower:]]', 'g')
) AS (u text[], l text[]);

Error: [42601] ROWS FROM() with multiple functions cannot have a column definition list
Hint: Put a separate column definition list for each function inside ROWS FROM().

SELECT u[1], l[1]
FROM ROWS FROM (
  regexp_matches('fooBarBaz', '[[:upper:]]', 'g') AS (u text[]),
  regexp_matches('fooBarBaz', '[[:lower:]]', 'g') AS (l text[])
);

Error: [42601] a column definition list is only allowed for functions returning "record"

I'm out of ideas. This has to be possible.

Documentation:

1 Answer 1

1

You need a table alias too

SELECT T.u[1], t.l[1]
FROM ROWS FROM (
  regexp_matches('fooBarBaz', '[[:upper:]]', 'g'),
  regexp_matches('fooBarBaz', '[[:lower:]]', 'g')
) AS t (u, l);
3
  • I've tried it with a table alias, too. The key was to omit the column types.
    – xehpuk
    Sep 13, 2020 at 8:39
  • Can you omit the table alias? Sep 13, 2020 at 11:39
  • Only when I also omit the column list. But then I'm back to square one. The syntax from your snippet is probably the only one that works.
    – xehpuk
    Sep 13, 2020 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.