A function performs some specified work, usually taking parameters as input. In terms of databases these may be specific to SQL or to the database vendor.
2
votes
1answer
120 views
PostgreSQL function definition “syntax error at or near +” [on hold]
Suppose a simple query:
(SELECT MAX(timestamp) FROM events e WHERE e.id < some_id)
+ ((SELECT MIN(timestamp) FROM events e WHERE e.id > some_id)
- (SELECT MAX(timestamp) FROM events e ...
0
votes
0answers
13 views
MySQL: Hierarchical query to get the direct and indirect child
I am just trying to modify the code as in MySQL: Tree-Hierarchical query
to get values inside single quotes and seems like the system hangs.
Not sure where I am going wrong..
Here is the code.
BEGIN
...
0
votes
0answers
31 views
For Postgres, how would I define a custom function to generate primary keys?
In postgres, primary keys are typically generated using a SEQUENCE. I am hoping to generate a unique random 12 character string for use as a primary key.
How would I do this in Postgres?
3
votes
1answer
58 views
'ERROR: text search dictionary “unaccent” does not exist' during CREATE INDEX?
I'm running PostgreSQL 9.3 on Mac OS X Yosemite.
I try to create an unaccent lowercase trigram index. To achieve it I did this:
mydb=# CREATE EXTENSION pg_trgm SCHEMA public VERSION "1.1";
...
1
vote
1answer
59 views
SQL query to sum a column prior to date and show all entries after that date
I have a database function that sum a column prior to a date and after that date show all entries one by one. that function return the data ordered by a common field and apply the sum by date every ...
0
votes
1answer
37 views
SQL Server Function For Calculating Average Excluding Outliers
I'm looking for a function that will let me take an average and exclude outliers. Basically something where:
1, 2, 1, 2, 7
Would average out to 1.5.
I'm going to implement this in a financial ...
0
votes
1answer
26 views
No records in Mysql.func table even though there are stored functions in server
When i am creating a user defined function in one of my mysql server, it's not creating any row in mysql.func table . Even though i have multiple functions in my mysql server , this table is empty . ...
0
votes
1answer
42 views
Joining two inline functions, slows considerably when filtering on second function
ok, So I have two inline functions that run quickly enough alone.
When I left join them like this
select *
from
Function1(-1) F1
left join
Function2(-1) F2 on F1.key = F2.key
When I run this I ...
0
votes
1answer
44 views
PostgreSQL - is it possible to use a plpgsql function in a bash script?
Target : What i'm trying to achieve is to restore a partial backup of a database (only certain rows that satisfied certain conditions have been backed up).
Problem : Since between the partial-backup ...
0
votes
1answer
63 views
Error with FUNCTION ( SQL Server 2008 ) - ''Cannot find either column…'
Friends,
I have this Function:
create function [dbo].[fn_xxx]
(
@Nome varchar(100),
@Descricao varchar(500)
)
RETURNS @tbDados table
(
Qtd int
)
as
Begin
...
0
votes
1answer
30 views
Function to handles firstname and lastname to a specific format
I'm trying to figuring out a way to show a name properly.
Let's say I have 2 variable :
@Firstname = 'Marc-Oliver'
@Lastname = 'Saint-Onge'
I would like to have name shown as;
M.-O. St.Onge
Or ...
0
votes
2answers
61 views
AVG function returns wrong average
I have a simple table with a column of type REAL.
There are 2 rows in the table and the values are 0.23 and 0.24. If I run the following:
SELECT AVG(MyColumn) FROM dbo.MyTable
I would expect to ...
0
votes
1answer
48 views
How to use MySQL stored function argument to refer to column name?
How can I use a function argument s to stand in for a column name in a query? I'm using mysql 5.5. I tried
SELECT count_distinct(last_name)
but it returns
ERROR 1054 (42S22): Unknown column ...
0
votes
1answer
53 views
Convert an interaction matrix into a ternary relation
I have what I'm calling a sparse "interaction matrix" of data in MS Excel that I wish to convert into a "ternary relation" (table) in Access.
The spreadsheet data are in this pattern:
Name Act-1 ...
-1
votes
1answer
28 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 ...
0
votes
0answers
35 views
Wrapping code in function is causing a huge delay
I have the following function:
CREATE FUNCTION [dbo].[FunctionScore]
(
@Param1
...
,@ParamN
)
RETURNS TABLE
RETURN
(
SELECT T1.[Col001]
,SUM(ISNULL(F1.[Pts], T2.[Pts])) ...
2
votes
1answer
46 views
How to Create Aggregate function in SQL Server 2008
My requirement is to create concatenate function so I am trying to create Aggregate function as shown
CREATE AGGREGATE [dbo].[Concatenate]
(@value [NVARCHAR](MAX))
RETURNS[NVARCHAR](MAX)
EXTERNAL ...
0
votes
1answer
27 views
Update statement with a function
I'm just curious. I was trying to optimize an update statement with a function
Ex
update circle set area = fnGetArea(radius) where ...<bunch of conditions>
This update went 5-10mins and when ...
0
votes
0answers
29 views
Using Code to Access Database Dump via pgAdmin
I looked over this question on how to import a data dump using pgadmin and found the following code:
CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$
tables RECORD;
gmdeclare
...
0
votes
1answer
36 views
Allow public use of functions without giving them database control
I came up with a set of functions that I would like to be accessed from a variety of places. SQL seemed like the best place for this due to accessibility, but I receive this error when trying to use ...
0
votes
1answer
42 views
Syntax Error In Postgresql Function [closed]
Why am I getting:
ERROR: syntax error at or near "'SELECT '"
In:
CREATE OR REPLACE FUNCTION GetListings (lon double precision, lat double precision, schemas name[])
RETURNS TABLE(
schema ...
2
votes
2answers
232 views
Is it possible to define a function within a stored procedure?
I have a stored procedure:
create proc sp_MyProc(@calcType tinyint) as
begin
-- some stuff collating data into #MyTempTable
if (@calcType = 1) -- sum
select A, B, C, CalcField = ...
1
vote
1answer
86 views
Next value for Sequence Function
Why does this give me the same value, four 1?
USE [TSQL2012]
GO
IF OBJECT_ID('dbo.sqlsequence', 'SO') IS NOT NULL
DROP SEQUENCE dbo.sqlsequence;
GO
CREATE SEQUENCE [dbo].[SQLSequence] AS INT
...
1
vote
2answers
332 views
Add function parameter to SQL query WHERE clause
I have a web application in java and it uses a query. I don't want to write the query into Java, so I made a function:
CREATE OR REPLACE FUNCTION testFunc(inputs text) RETURNS TABLE(...) AS
$$
...
1
vote
2answers
145 views
Function extract() from date returns double precision value
Why does the Postgres function extract() return double precision from date value?
Only reason I could imagine is, there are some locales using calendars (those not Gregorian calendars and supported ...
1
vote
1answer
78 views
Implement a Parent-Level Count in Binary Search Tree
Based on this question, have any way to implement a parent level count?
Find highest level of a hierarchical field: with vs without CTEs
Example table:
+----+-----------+
| id | parent_id |
...
0
votes
0answers
35 views
Performance decline function vs command window
I have a function that I first wrote as a serious of commands. In the SQL command window when executed, those steps take about 8 seconds to return the data set. When those same commands are turned ...
1
vote
1answer
266 views
Why “SET LOCAL statement_timeout” does not work as expected with PostgreSQL functions?
My understanding is that PostgreSQL functions are executed similar to a transaction. However, when I tried to "SET LOCAL statement_timeout" within a function, it did not work. Here's how it works ...
2
votes
0answers
28 views
PLSQL using cursors or functions to query repeated data
Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)?
Whats the difference?
What are the best ...
2
votes
2answers
110 views
Call UPSERT function multiple times to different inputs
I have a function:
merge_vehicles(vid, cid, vname, reg_no, name, name_1st)
Can i call it multiple times on inputs like
(2335, 55, '246BDH', '246BDH', '811', 1),
(2336, 55, '038THX', '038THX', ...
1
vote
2answers
218 views
Assign to NEW by key in a Postgres trigger
In the trigger body, how can I assign a value to NEW by it's field name?
That's what I want to do:
some_key = "some_column";
NEW[some_key] = 5;
0
votes
1answer
145 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 ...
0
votes
2answers
167 views
Returning true or false in a custom function
I have this mysql table schema
CREATE TABLE `nxt_records` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`tel_number` VARCHAR(50) NULL DEFAULT NULL,
`nxt_status` VARCHAR(50) NULL DEFAULT NULL,
...
0
votes
0answers
76 views
postgres: access to schema objects within a function
Here's my problem:
I have Schema A, owned by User 1, that owns all objects.
I've created schema B, owned by User 2.
I have granted User 2 all privileges to the objects owned by User 1.
When logged ...
0
votes
1answer
125 views
Explain doesn't show indexed column as key
Consider the following table structure
CREATE TABLE `games` (
`game_id` bigint(20) NOT NULL AUTO_INCREMENT,
`players_no` tinyint(4) DEFAULT NULL,
`startedon` datetime NOT NULL DEFAULT '0000-00-00 ...
0
votes
1answer
38 views
column value is not increment after 10
I tried below 2 codes.... showing same error
1.SELECT columnname FROM tablename ORDER BY columnname DESC LIMIT 1
SELECT MAX(columnname ) AS max FROM tablename
I am trying to increment value of ...
0
votes
0answers
105 views
Why would call to scalar function inside a Table Value Function be slower than outside the TVF?
I am writing a Table Value Function, calling the function takes 10x as long as directly running the code. I traced this to a call to a multi-line scalar function inside the TVF. The call to the ...
2
votes
1answer
81 views
How to apply a regex on a column fetched from DB
I am using IBM DB2.
So, I am retrieving a column named SSN from a table. I have to apply the following regex on that column:
([1-57-8][0-9]{2}
|0([1-9][0-9]
|[0-9][1-9])
|6([0-57-9][0-9]
...
4
votes
0answers
92 views
Why is it @@SERVERNAME but not @@DBNAME? [closed]
I'm just curious: To get the server name, I use
SELECT @@SERVERNAME
but to get the current database name, I use
SELECT DB_NAME()
Technically, both seem to be scalar functions: @@SERVERNAME is ...
1
vote
1answer
121 views
What does exec(text) do?
I just found the following function in two RDS PostgreSQL 9.3 DBs:
--
-- Name: exec(text); Type: FUNCTION; Schema: public; Owner: adam
--
CREATE FUNCTION exec(text) RETURNS text
LANGUAGE plpgsql
...
0
votes
0answers
64 views
Index on fields with a function
I have a log table. It logs visitor data (time of visit, browser, window size ...).
I want to create reports. I use a query like this (MySQL):
SELECT
COUNT(*) as raw_views ...
FROM logs
WHERE ...
-1
votes
4answers
192 views
Index on 'GROUP BY' with functions
I have a log table. It logs visitor data (time of visit, browser, window size ...).
I want to create reports. I use a query like this (MySQL):
SELECT
COUNT(*) as raw_views ...
FROM logs
WHERE ...
0
votes
1answer
49 views
different outcome based on function execution method
I am mainlining a legacy system and realize that the answer to this may take a deeper explanation of the code but before I go that far I wanted to make sure the solution wasn't something more simple.
...
2
votes
1answer
178 views
Prevent postgres function of running more than 1 instance at the same time
DBMS: Postgres 9.3.4
OS: Debian 7
I have a plpgsql function that will be put in crontab to run every 15 minutes. The function should finish in about 8 minutes, but just in case it takes more than 15 ...
0
votes
0answers
37 views
Function performance issue
This one is puzzling me since yesterday and maybe someone could shed some light on this.
We've got this table that records GPS data. We're currently doing some overloading tests to see how the ...
2
votes
1answer
1k views
How to pass a parameter into a function
At the moment I have a view created, see it here in the answer. How can I create a function based on that crosstab() query , so I can pass a date, and get data for the specific date?
Also is it good ...
1
vote
3answers
541 views
How to log DML statements executed by a pl/pgsql function?
I have a pl/pgsql function (see below) that lists some fields and clears their contents using dynamically constructed UPDATE commands.
When I set log_statement = 'mod', I can see nothing on the log ...
3
votes
1answer
1k views
Use NOLOCK hint when calling table valued function
Suppose the following function:
CREATE FUNCTION [dbo].[ufnTest]()
RETURNS TABLE
AS RETURN SELECT 1 AS Nr
My actual function will select actual data from many different tables. I (mostly) understand ...
0
votes
1answer
30 views
Mysql functions for web apps
I have few questions about using functions in a MySQL database for plugins and extensions of web apps like Magento or Wordpress. Mostly this is for a few extensions I want to do for Magento. The ...
1
vote
1answer
171 views
Postgres : Executing SELECT within a transaction does not return most recent rows
Given a table layout that looks like this
inv_items
=========
* id bigserial
sku character varying(22)
name character varying(32)
...
inv_items_stock
===============
item_id bigint (inv_items)
qty ...
