performing an UPDATE on a table-valued function

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

performing an UPDATE on a table-valued function

Timothy Stack
The current table-valued function feature seems to only work for
SELECT statements.  Was any thought given to whether updates
would be supported for table-valued functions?  It seems like it's
technically possible and could be made to work right now with
this awkward syntax:

  UPDATE foo SET col0 = 'bar' WHERE hidden_field = 'baz'

Having the real syntax, like the following, would be nice though:

  UPDATE foo('baz') SET col0 = 'bar'

thanks,

tim stack
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: performing an UPDATE on a table-valued function

Simon Slavin-3

On 16 Apr 2017, at 10:57pm, Timothy Stack <[hidden email]> wrote:

>  UPDATE foo SET col0 = 'bar' WHERE hidden_field = 'baz'
>
> Having the real syntax, like the following, would be nice though:
>
>  UPDATE foo('baz') SET col0 = 'bar'

How would it know that 'baz' is a value for the column "hidden_field" rather than some other column ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: performing an UPDATE on a table-valued function

Richard Hipp-3
On 4/16/17, Simon Slavin <[hidden email]> wrote:

>
> On 16 Apr 2017, at 10:57pm, Timothy Stack <[hidden email]>
> wrote:
>
>>  UPDATE foo SET col0 = 'bar' WHERE hidden_field = 'baz'
>>
>> Having the real syntax, like the following, would be nice though:
>>
>>  UPDATE foo('baz') SET col0 = 'bar'
>
> How would it know that 'baz' is a value for the column "hidden_field" rather
> than some other column ?

Table-valued functions are just virtual tables that allow parameters
to be used as constraints against the "hidden" columns of the virtual
table.  The parameters match in order.  The first parameter matches
against the first hidden column, and the second parameter matches
against the second hidden column, and so forth.

That is, I'm guessing, what the OP means.

Whether or not the suggest is a good idea is a different matter.  I'm
skeptical.  A function is typically not an l-value and is hence not
typically something that can be modified.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: performing an UPDATE on a table-valued function

Timothy Stack
On Sun, Apr 16, 2017 at 3:40 PM, Richard Hipp <[hidden email]> wrote:

> On 4/16/17, Simon Slavin <[hidden email]> wrote:
> >
> > On 16 Apr 2017, at 10:57pm, Timothy Stack <[hidden email]>
> > wrote:
> >
> >>  UPDATE foo SET col0 = 'bar' WHERE hidden_field = 'baz'
> >>
> >> Having the real syntax, like the following, would be nice though:
> >>
> >>  UPDATE foo('baz') SET col0 = 'bar'
> >
> > How would it know that 'baz' is a value for the column "hidden_field"
> rather
> > than some other column ?
>
> Table-valued functions are just virtual tables that allow parameters
> to be used as constraints against the "hidden" columns of the virtual
> table.  The parameters match in order.  The first parameter matches
> against the first hidden column, and the second parameter matches
> against the second hidden column, and so forth.
>
> That is, I'm guessing, what the OP means.
>

​Yes, that is what I mean.​

Whether or not the suggest is a good idea is a different matter.  I'm
> skeptical.  A function is typically not an l-value and is hence not
> ​​
> typically something that can be modified.
>

​Let's take the 'carray' extension[1] as an example​.  If I can read from
an array in memory with the following statement:

  SELECT * FROM carray(0x7b3830, 10);

​Why shouldn't ​I be able to update that array using a similar syntax?

​tim​

[1] - https://sqlite.org/carray.html


>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: performing an UPDATE on a table-valued function

Hick Gunter
In reply to this post by Timothy Stack
A TVF is just a "calling convention" for abbreviating a specific SELECT on a virtual table. It implies neither mutability of the returned values, nor persistence beyond the scope of the statement. Indeed the carray() example you give later is an eponymous ephemeral table, i.e. it does not require a CREATE VIRTUAL TABLE statement, but springs into existence by virtue of being mentioned (technically: it is created in the xFilter method) and "evaporates" when the cursor is closed.

If you want a mutable, persistent non-native table, then please document this fact by executing a CREATE VIRTUAL TABLE statement.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Timothy Stack
Gesendet: Sonntag, 16. April 2017 23:57
An: SQLite mailing list <[hidden email]>
Betreff: [sqlite] performing an UPDATE on a table-valued function

The current table-valued function feature seems to only work for SELECT statements.  Was any thought given to whether updates would be supported for table-valued functions?  It seems like it's technically possible and could be made to work right now with this awkward syntax:

  UPDATE foo SET col0 = 'bar' WHERE hidden_field = 'baz'

Having the real syntax, like the following, would be nice though:

  UPDATE foo('baz') SET col0 = 'bar'

thanks,

tim stack
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: performing an UPDATE on a table-valued function

Timothy Stack
On Tue, Apr 18, 2017 at 12:04 AM, Hick Gunter <[hidden email]> wrote:

> A TVF is just a "calling convention" for abbreviating a specific SELECT on
> a virtual table. It implies neither mutability of the returned values, nor
> persistence beyond the scope of the statement. Indeed the carray() example
> you give later is an eponymous ephemeral table, i.e. it does not require a
> CREATE VIRTUAL TABLE statement, but springs into existence by virtue of
> being mentioned (technically: it is created in the xFilter method) and
> "evaporates" when the cursor is closed.
>

​Yes, I'm aware of this and have implemented table-valued functions and
many other SQLite extensions.  I understand how things work.  I raise
the question because it seems possible in other SQL languages[1][2].
And, to me at least, it makes logical sense (hence the carray example).

If you want a mutable, persistent non-native table, then please document
> this fact by executing a CREATE VIRTUAL TABLE statement.
>

​I'm ​not sure what you're saying here.  I'm asking for the SQLite syntax
to be changed to support table-valued functions for INSERT/UPDATE
statements.

​tim​

[1] - http://sqlblog.com/blogs/greg_low/archive/2011/05/22/
update-against-a-table-valued-function-when-declared-inline.aspx
​[2] - http://stackoverflow.com/questions/16568454/is-a-table-
valued-function-updatable


-----Ursprüngliche Nachricht-----

> Von: sqlite-users [mailto:[hidden email]]
> Im Auftrag von Timothy Stack
> Gesendet: Sonntag, 16. April 2017 23:57
> An: SQLite mailing list <[hidden email]>
> Betreff: [sqlite] performing an UPDATE on a table-valued function
>
> The current table-valued function feature seems to only work for SELECT
> statements.  Was any thought given to whether updates would be supported
> for table-valued functions?  It seems like it's technically possible and
> could be made to work right now with this awkward syntax:
>
>   UPDATE foo SET col0 = 'bar' WHERE hidden_field = 'baz'
>
> Having the real syntax, like the following, would be nice though:
>
>   UPDATE foo('baz') SET col0 = 'bar'
>
> thanks,
>
> tim stack
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: [hidden email]
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: performing an UPDATE on a table-valued function

Timothy Stack
In reply to this post by Richard Hipp-3
On Sun, Apr 16, 2017 at 3:40 PM, Richard Hipp <[hidden email]> wrote:

> > On 16 Apr 2017, at 10:57pm, Timothy Stack <[hidden email]>
> > wrote:
> >
> >>  UPDATE foo SET col0 = 'bar' WHERE hidden_field = 'baz'
> >>
> >> Having the real syntax, like the following, would be nice though:
> >>
> >>  UPDATE foo('baz') SET col0 = 'bar'
>
> Whether or not the suggest is a good idea is a different matter.  I'm
> skeptical.  A function is typically not an l-value and is hence not
> typically something that can be modified.


​Richard, if I made the necessary changes to the parser, docs, and updated
the carray extension as an example, would you accept the patch?

thanks,

tim​


> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: performing an UPDATE on a table-valued function

Richard Hipp-3
On 4/19/17, Timothy Stack <[hidden email]> wrote:
>
> ​Richard, if I made the necessary changes to the parser, docs, and updated
> the carray extension as an example, would you accept the patch?
>

Probably not.  But an actual demonstration showing that your proposed
enhancement is useful and does not impact performance for the
(literally) billions of people who do not use your enhancement would
be one step toward getting the enhancement in the core.

(1) SQLite is public domain.  In order to keep it that way, we are
unable to accept contributions from unknown persons on the internet.

(2) Most of the work involved in an enhancement is supporting it for
the next 37 years.

(3) One of the most important jobs of a project leader is learning
when to say "no" and preventing feature-creep and loss of focus.  I
have not done a great job of that in the past.  I'm trying to do
better.  New features need to be a clear win before they are accepted.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users