Selecting Postgres functions

[Adam Rutkowski]

Hello,

I am in need of using postgres functions, and am wondering if there's a nice way 

to do that with Ecto.

The most trivial example I can think of would be fetching `nextval` from a sequence.

hq1=# create sequence some_serial;

CREATE SEQUENCE

hq1=# select * from nextval('some_serial');

 nextval

---------

       1

(1 row)

Obviously I can't do

"nextval('some_serial')" |> select([s], s.nextval)

because some_serial gets quoted and Postgres barfs.

So right now I'm calling Ecto.Adapters.SQL.query directly, which of course works,

but I have trouble figuring out how to achieve the proper abstraction and map

PL/pgSQL function results to Ecto.Schema structs, "the Ecto way".

Kind regards,

Adam

[José Valim]

It seems something I would honestly just expose in the repository:

defmodule Repo do

  def next_val(value) do

    Ecto.Adapters.SQL.query(...)

  end

end

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/a78905bc-bd43-48b8-a534-b96c92a7c934%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Adam Rutkowski]

Right, that definitely makes sense. Except I'll be dealing with much complex functions basically returning large tables. I guess support for that is too postgres-specific to be even considered a feature candidate?

You received this message because you are subscribed to a topic in the Google Groups "elixir-ecto" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/elixir-ecto/MsU4wevbYsM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAGnRm4Jfr5BdJ7fTC6ba9jH769k%2BTWrRPhs%2B1Q6gz0uB%2B0x0Jw%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--

/A.

[Gleb Arshinov]

Adam,

Can you give an example of a more complex function returning table
you'd want to use?

Best regards,

Gleb

> https://groups.google.com/d/msgid/elixir-ecto/CAPVfgHsGC%2BCXeqdxu1aKTgvHCCBaJN3KD8zhXJ_8B-AAR_WAjA%40mail.gmail.com.

[Adam Rutkowski]

Hi Gleb,

Sure, for example recursive, parametrised tree traversal.

Cheers

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CACZNi5_dGKvcyMhRYd2wm6useuMT6J%2BEAeJUPN_xS%2BRmsEW_Pg%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--

/A.

[Matt Widmann]

The following worked for me on the console:

from(u in User, select: %{num: fragment("10 * 10 / 3")}) |> Repo.one

[debug] SELECT 10 * 10 / 3 FROM "users" AS u0 [] OK query=1.2ms

%{num: 33}

Would it work to add your `nextval('some_seq')` inside the fragment above? If it works, it seems you'll have to use some dummy table which you won't actually be hitting like I did with my users table...