The user-defined-functions tag has no usage guidance.
0
votes
0answers
17 views
plpy.execute('…') statements not in logs
How can I get the SQL statements executed by plyp into the postgres log files?
CREATE FUNCTION history_monitor() RETURNS trigger
LANGUAGE plpythonu
AS $_$
...
plpy.execute('...')
$_$;
...
3
votes
1answer
74 views
Need for recursive lookup, stuck in query design
In PostgreSQL 9.4, here is my table:
with r(pk, userid,partneruserid) as
(values
(1,1,2),
(2,1,3),
(3,1,6),
(4,2,5),
(5,2,6),
(6,3,1),
(7,4,1),
(8,5,3),
(9,6,2),
(10,6,3)
) select * from r;
In a ...
0
votes
1answer
39 views
When to use stored procedure / UDF? [closed]
I'm confused about usage of SP / UDF. Typically, it is also possible to write code in programs outside of database. So is there any general advice to decide when to use them?
0
votes
0answers
30 views
Are MySQL UDF ACID compliant?
I had a Client Requirement wherein i had to write a MySQL UDF to generate AlphaNumeric Values. The input parameters for the Function where 1.Prefix Character 2.The Length of the AlphaNumeric Value 3. ...
0
votes
0answers
24 views
Unable to update table in MySQL Function
I have a requirement, where in i have create a function which will generate Alphanumeric Values based on the two Inputs..1. A string 2. A Digit.
Like If the string is Mem..then Mem001,Mem002 and If ...
12
votes
2answers
226 views
How to implement a Set based algorithm/UDF
I have an algorithm that I need to run against every row in a table with 800K rows and 38 columns.
The algorithm is implemented in VBA and does a bunch of math using values from some columns to ...
0
votes
1answer
21 views
Cannot find either column “dba” or the user-defined function or aggregate “schema.functionName”, or the name is ambiguous
I created a function and granted select on [dba].[ufn_track_dailies] to public. The function shows up under table valued functions, but when I try to use it I get an error:
select ...
4
votes
1answer
129 views
Performance problems with TOP and user defined functions in views
I have problem with performance of queries on simple query on view.
To keep things simple, assume my view is defined as follows:
CREATE VIEW [mon].[ViewDevicesWithGroups]
AS
SELECT Id, Name, ...
2
votes
1answer
202 views
Password generator function
What's the best way of creating a password generator user defined function function in SQL Server if it is not possible to use non deterministic funtions within functions?
I would like to create a ...
1
vote
1answer
53 views
Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?
I experienced very strange problem today with TSQL function.
The function has several parameters (int and bit). One of the (int) is set with value NULL.
When calling a function with NULL inside the ...
0
votes
0answers
10 views
Are User Defined functions always allowed on cloud services?
The question is, what is the policy on user defined functions on cloud services like Amazon RDS or Azure? Is it possible to load any User Defined Function? If so, isn't it possible to load some ...
2
votes
0answers
35 views
Is there a way to determine if a postgres function depends on a user-defined type?
I am writing a Ruby script to handle functions and user-defined types in Postgres. I want to make sure, that before a function is created in the database, if it uses a user-defined type, than this ...
0
votes
1answer
43 views
Usage of ExecSQL in UDF?
Problem:
is it possible to use sp_executesql or EXEC inside of a user defined function?
Thanks!
2
votes
2answers
102 views
How can I query a remote server using a local function?
I am querying a remote server for which I only have read permissions. I set up a local server with a UDF (since I cannot create one on the remote) that I would like to utilize for the query. When I ...
0
votes
0answers
46 views
MySQL Queries in UDF
I would like to create a MySQL UDF (i.e. User Defined Function) that embeds
a query; for instance, suppose the UDF is named foobar:
mysql> SELECT foobar();
When foobar function receives the ...
0
votes
2answers
86 views
When doing an update on a set of records, how do i get previous updates to show on current row to update in UDF [closed]
SO i have a rowset of 10 UIDs. They are all dupes, lets say DUPEUID. I have a UDF that will take the UID, check it against the table to see if there is a dupe, and update the UID to DUPEUID01.
The ...
1
vote
0answers
16 views
CLR UDT SCH-M blocking
I’m hoping someone can provide some guidance on a problem I’m having with schema locking on C# CLR UDT’s. I’ve been on the Internet for an extended period of time researching my problem without ...
-2
votes
1answer
40 views
Postgresql - Stuck building a plpgsql function [closed]
I am a beginner in plpgsql and i am stuck coding a function.
I need a function that does the following:
Giving a table, a id_field in that table and another field in that table it does:
ORIGINAL ...
2
votes
1answer
3k views
How to grant permissions on a table-valued function
Am I doing it right...?
I have a function that returns money...
CREATE FUNCTION functionName( @a_principal money, @a_from_date
datetime, @a_to_date datetime, @a_rate float ) RETURNS money AS ...
0
votes
2answers
312 views
Using output value of column from function for another column in SQL*Loader
I have asked the same question on StackOveflow. Do not know, if this site is linked to that or not and thus whether that question will be seen by professionals here, so I am adding it here. Please ...
2
votes
1answer
699 views
How to return a record from function, executed by INSERT/UPDATE rule (trigger)?
I'm having some issue when I'm trying to join two tables in a single view (for migrating code we want to split one big record into two records). I'm using Postgres 9.4!
Here is an SQL fiddle.
The ...
1
vote
1answer
202 views
How to join a table with a table valued function?
I have a user defined function:
create function ut_FooFunc(@fooID bigint, @anotherParam tinyint)
returns @tbl Table (Field1 int, Field2 varchar(100))
as
begin
-- blah blah
end
Now I want to join ...
1
vote
0answers
224 views
How Do I Get DB2 NoSQL User-Defined Functions to Work
I am using NoSQL in DB2 10.5 FP4. I am creating and connecting to the database with the 'db2admin' administrator user ID on my Windows machine. I also used the 'db2 attach to DB2_01 as db2admin' ...
5
votes
2answers
306 views
How do I create a user-defined aggregate function in MySQL (redux)?
OK, I'm probably going to get banned for asking the same question a second time, but I'm an incredible newbie here and I was told I don't have enough of a reputation to comment on the original ...
1
vote
1answer
482 views
How Do I Grant Permissions to Replace a Function?
I am getting the following error message (when trying to enable NOSQL in DB2 10.5, although I don't think that's really the issue):
Error code: -551 DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501,
...
0
votes
1answer
246 views
MySQL UDF - wrong ELF class: ELFCLASS32
I'm having a problem creating a MySQL udf on my server (I was able to do it locally on OS X). I am compiling it like this on the server:
gcc $(mysql_config --cflags) -I /usr/include/mysql -I. -shared ...
1
vote
1answer
34 views
Handling exceptions from inside a function
I need to add a trigger after insert or update on a table so that, where some row is updated and a certain condition is met, then some other table needs to be updated. However, that last update ...
2
votes
3answers
2k views
Can I use PHP function strtotime() in SQL queries?
I have this table, and I need to use a PHP function in the query.
id time lastTime
1 08:02
2 08:04
3 08:30
I am using this query with the strtotime(), and it's not working:
...
-1
votes
1answer
173 views
View vs TableValued Function vs SubQuery
I have a scenario where i need to
join with 3 tables to get the value for one field
for second field join with 4 tables
for third field join with 3 tables
all above joins are inner and if data ...
3
votes
1answer
340 views
Is support for Parallel Scalar UDF a reasonable feature request?
It is fairly well documented that scalar UDF's force an overall serial plan.
Running functions in parallel
Given a large number of rows coming into a point in the pipeline where a UDF must be ...
0
votes
0answers
68 views
Slow search times with functions despite index?
I'm aware that this question might be under the guidelines of being closed, but I'm out of ideas at the moment on how to optemize this table any further.
Given a have a few billion records with ...
0
votes
1answer
100 views
Can queries containing deterministic user defined functions be cached in mysql query cache?
According to the mysql 5.6 docs:
A query also is not cached under these conditions:
...
It refers to user-defined functions (UDFs) or stored functions.
However, I have seen claims that if ...
1
vote
2answers
256 views
Latitude and Longtitude Distance Calculation [closed]
I'm using MySQL and I am trying to calculate the distance in meters between two different longitude, latitude coordinates. I wrote a stored function in MySQL but it calculates just 1 for each row in ...
2
votes
1answer
371 views
Getting Visual Studio to deploy a UDF to Return NVARCHAR(MAX)
I have a SQLCLR stored procedure with the following signature in Visual Studio 2013:
[SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
public static ...
2
votes
1answer
2k views
Automatic conversion of anonymous records returned from an UDF to well known table-type
Proprietary code (that we cannot change) has a bunch of user defined functions of the type:
create or replace function f() returns record as $$ ... $$
which we call in the following way (for ...
2
votes
2answers
5k views
Insert values from a record variable into a table
I am developing a user-defined function that takes two arguments:
create or replace function gesio(
events_table_in regclass,
events_table_out regclass)
returns void as $$ ... $$
...
3
votes
2answers
3k views
Why cannot I call a table function in iSeries DB2 that I just created?
Using iSeries Navigator 'Run an sql script' window I created a new table function.
When I go to the Functions branch under the schema where it was created, I see the function among the other few. But ...
4
votes
1answer
2k views
MySQL possibility to create global routines (stored procedures and/or functions)
Is it possible to somehow define globally available routines? It seems like every routine must be created in a scope of the database.
When I tried to create a routine from console (without prior ...
1
vote
2answers
101 views
Optimizing UDF - Is Using DUAL Expensive
I am new to Oracle technology (primarily versed in SQL Server) and I am having trouble with the performance of a UDF.
The purpose of the UDF is to take some inputs for an "event", and look up an ...
12
votes
1answer
4k views
SQL injection in Postgres functions vs prepared queries
In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection?
Are there particular advantages in one approach over the other?
1
vote
0answers
158 views
Inline TVF with One Row Result
I have several times converted scalar functions into inline tvf. Usually this is the basic design I used in inline tvf:
CREATE FUNCTION TVF(@ID INT)
RETURNS TABLE
AS
RETURN(
WITH BASE AS(
...
1
vote
0answers
262 views
MariaDB CUstom Functions
In MySQL, I wrote functions in C++, compiled them as .so, and added them to MySQL. I am trying to find a website that shows an example for doing the same in MariaDB. I cannot find any instructions, ...
2
votes
0answers
172 views
When converting a table valued function to inline, why do I get a lazy spool?
I have a table valued function that I want to convert to inline to improve performance.
It sets the value of variables to the value four bit columns in one row of a table (looking up the row using a ...
0
votes
1answer
143 views
Different Results between stored proc and udf
I am getting different results between my stored proc and my SVF.
The jist of of the both of them is that, it queries a remotely linked server, jams those results into a temp table/table variable, ...
10
votes
1answer
1k views
Scalar function used in calculated column, what's the cleanest way to update the function?
I want to update a scalar function in our database. This function is used for several calculated columns in a key table so I get a dependency error if I try to update it. I can explicitly remove the ...
