postgresql – Function in Postgres that uses the table where I’m passing the function inside the function body?

I’ve got two tables, one with street_codes and street_numbers (addresses) and another with sidewalks_keys that correspond to each address (address_sidewalks_keys). I would like to create a function in Postgres that when passing the parameters of street_code, from_number and to_number (which determine street segments), I get back all the sidewalks_keys that lie within based on what it joins and aggregate from rows in the table address_sidewalks_keys. It should perform something like the following query (with those conditions) but I’m clueless about how should I use the parameters of the function as if they were a table on their own to use as columns to select from and aggregate with columns from another table. I’m new to PostgreSQL Stored Procedures and I don’t even know if this is the most efficent/preferred way. My idea is to use the function inside a Python app with Pandas in order to automate stuff that is not in a RDBMS.

WITH addresses AS ( 
SELECT a.*
FROM addresses a
WHERE a.street_code ~ '^(0-9)+$' IS TRUE
AND a.from_number ~ '^(0-9)+$' IS TRUE
AND a.to_number ~ '^(0-9)+$' IS TRUE
),
--filtered table without duplicated sidewalk_keys for the same row/from-to-address--
filtered_table as (SELECT DISTINCT ON (c.id, c.street_name, c.from_number, c.to_number, b.sidewalk_key) c.*, b.sidewalk_key 
FROM addresses c
LEFT JOIN address_sidewalks_keys b
--conditions depending wheter from-to-address is only odds, evens or both--
    ON b.street_code = c.street_code::integer AND 
    (b.street_number BETWEEN c.from_number::integer AND c.to_number::integer)
        AND CASE 
            WHEN c.from_number::integer % 2 = 0 AND c.to_number::integer % 2 = 0 THEN  b.street_number % 2 = 0
            WHEN c.from_number::integer % 2 <> 0 AND c.to_number::integer % 2 <> 0 THEN  b.street_number % 2 <> 0
            ELSE b.street_number = b.street_number
            END
    )
--final table--
SELECT d.id, d.street_code, d.street_name, d.from_number, d.to_number, array_to_string(array_agg(d.sidewalk_key), ' / ') as sidewalks_keys, count(d.sidewalk_key) as count_sidewalks
FROM filtered_table d 
--aggregator--
GROUP BY d.id, d.street_code, d.street_name, d.from_number, d.to_number

Each sidewalk_key is linked to a street_number that has a unique street_code and number. Most of the time, my addresses table comes wrong inputs like the ones I show in this example that force me to assign varchar variables and then filter and cast to integers (first problem). Moreover, street numbers in this addresses table can include both even and odd numbers (both side of streets) so I need to differentiate cases on my joins (second problem). Then one sidewalk_key can be linked to many addresses/combination of street_code and street_number, so I have to perform a Select Disctinct as I am only interested in having the found sidewalk_key once if it lies within the street segment of my addresses table (third problem).

Here are some examples of my tables:

CREATE TABLE public.addresses (
    id integer,
    street_code character varying,
    street_name character varying,
    from_number character varying,
    to_number character varying
);

ALTER TABLE public.addresses OWNER TO postgres;

INSERT INTO public.addresses VALUES (4568, '7136', 'GUEVARA, TRINIDAD', '22', '22');
INSERT INTO public.addresses VALUES (4567, '21004', 'TACUARI', '1501', '1600');
INSERT INTO public.addresses VALUES (3051, '17118', 'POTOSI', '4303', '4315');
INSERT INTO public.addresses VALUES (1497, '#N/A', 'PLAZOLETA SANTA MARIA DE LOS BUENOS AIRES', NULL, NULL);
INSERT INTO public.addresses VALUES (6273, '7088', 'GUAMINI', 'Y ZELADA', 'Y ZELADA');


CREATE TABLE public.address_sidewalks_keys (
    id integer,
    sidewalk_key character varying,
    street_name character varying,
    street_number integer,
    street_code integer
);


ALTER TABLE public.address_sidewalks_keys OWNER TO postgres;

INSERT INTO public.address_sidewalks_keys VALUES (20520750, '017-029-001A-17118-1', 'POTOSI', 4303, 17118);
INSERT INTO public.address_sidewalks_keys VALUES (20520751, '017-029-001C-17118-1', 'POTOSI', 4307, 17118);
INSERT INTO public.address_sidewalks_keys VALUES (20520752, '017-029-001C-17118-1', 'POTOSI', 4309, 17118);
INSERT INTO public.address_sidewalks_keys VALUES (20520754, '017-029-001B-17118-1', 'POTOSI', 4315, 17118);
INSERT INTO public.address_sidewalks_keys VALUES (20652615, '008-033-005E-21004-1', 'TACUARI', 1506, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652616, '008-010-001B-7026-1', 'TACUARI', 1509, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652617, '008-010-001B-7026-1', 'TACUARI', 1511, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652618, '008-010-001B-7026-1', 'TACUARI', 1513, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652619, '008-010-029-21004-1', 'TACUARI', 1515, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652620, '008-033-005E-21004-1', 'TACUARI', 1516, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652621, '008-010-029-21004-1', 'TACUARI', 1517, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652622, '008-010-029-21004-1', 'TACUARI', 1519, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652623, '008-033-005E-21004-1', 'TACUARI', 1520, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652624, '008-010-029-21004-1', 'TACUARI', 1521, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652625, '008-010-028-21004-1', 'TACUARI', 1523, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652626, '008-033-005E-21004-1', 'TACUARI', 1524, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652627, '008-010-027-21004-1', 'TACUARI', 1531, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652628, '008-010-027-21004-1', 'TACUARI', 1535, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652629, '008-010-027-21004-1', 'TACUARI', 1537, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652630, '008-033-006A-21004-1', 'TACUARI', 1538, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652631, '008-010-027-21004-1', 'TACUARI', 1539, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652633, '008-010-026-21004-1', 'TACUARI', 1541, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652634, '008-033-006A-21004-1', 'TACUARI', 1542, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652635, '008-010-026-21004-1', 'TACUARI', 1543, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652636, '008-010-026-21004-1', 'TACUARI', 1551, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652637, '008-010-025-21004-1', 'TACUARI', 1553, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652638, '008-010-025-21004-1', 'TACUARI', 1555, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652639, '008-010-025-21004-1', 'TACUARI', 1557, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652640, '008-010-024-21004-1', 'TACUARI', 1559, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652642, '008-033-006B-21004-1', 'TACUARI', 1560, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652643, '008-010-024-21004-1', 'TACUARI', 1561, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652644, '008-010-024-21004-1', 'TACUARI', 1563, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652645, '008-010-024-21004-1', 'TACUARI', 1565, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652646, '008-033-007-21004-1', 'TACUARI', 1568, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652647, '008-010-024-21004-1', 'TACUARI', 1569, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652648, '008-033-007-21004-1', 'TACUARI', 1570, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652649, '008-033-007-21004-1', 'TACUARI', 1572, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652650, '008-033-008-21004-1', 'TACUARI', 1574, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652651, '008-010-023-21004-1', 'TACUARI', 1575, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652653, '008-033-008-21004-1', 'TACUARI', 1580, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652654, '008-010-023-21004-1', 'TACUARI', 1581, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652655, '008-033-008-21004-1', 'TACUARI', 1582, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652656, '008-010-022-21004-1', 'TACUARI', 1583, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652657, '008-010-022-21004-1', 'TACUARI', 1585, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652658, '008-033-009-21004-1', 'TACUARI', 1586, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652659, '008-033-009-21004-1', 'TACUARI', 1588, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652660, '008-010-022-21004-1', 'TACUARI', 1589, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652661, '008-033-009-21004-1', 'TACUARI', 1590, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652662, '008-010-021-21004-1', 'TACUARI', 1591, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652663, '008-010-021-21004-1', 'TACUARI', 1593, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652664, '008-033-010A-21004-1', 'TACUARI', 1594, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652665, '008-010-021-21004-1', 'TACUARI', 1595, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652666, '008-033-010A-21004-1', 'TACUARI', 1596, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652667, '008-010-021-21004-1', 'TACUARI', 1597, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652668, '008-033-010A-21004-1', 'TACUARI', 1598, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20652669, '008-010-019A-21004-1', 'TACUARI', 1599, 21004);
INSERT INTO public.address_sidewalks_keys VALUES (20935240, '001-072-001B-13008-1', 'GUEVARA, TRINIDAD', 22, 7136);

Design – Advantages of passing function arguments through a single structure

An employee has started advocating a style for our user-related APIs, where each function uses a single structural parameter that contains all the real parameters. That is, instead of:

void fn(int foo, bool bar);

we should do:

typedef struct {
    int foo;
    bool bar;
} fn_args_t;

void fn(fn_args_t args);

I've never seen this style before. He argues that it is easy to add new parameters later without having to update callers, although I am concerned that it is also easy to omit an argument (either an existing one that was just overlooked or one that was added later you want to pass nonzero for).

Is there a name for this style? For and against? It seems like a bad sign that I have never used it.

Design – Is argument passing to a constructor always considered DI?

When I start using DI more in my project design, I ask the same question:

"If I pass this object to this constructor, does this new object have a dependency now?"

For example, if I had:

class App:

    ROOT_DIR = Path('some_dir')
    SOME_CFG = Path(ROOT_DIR, 'some_cfg.cfg')

    def __init__(self, config: Config) -> None:
        self.config = config

class Config:

    def __init__(self, file: Path) -> None:
        self.file = file

From that, App has a dependency on Config there Config is responsible for all configuration tasks.

Where I start to question is how Config is constructed: Config(App.SOME_CFG).

So when I pass something to the constructor of ConfigDoes that mean that it has a dependency?

SQL Server – Passing Variable Values ​​to Stored Procedures

How are the values ​​in variables passed to a stored procedure?

I have a stored procedure that accepts the database name and the name of the source table and creates a new table based on the source table. I have the database name and the name of the source table in variables. I want to pass the values ​​of these variables to the stored procedure. I tried the following without success:

exec createtable (@DbName, @SrcTblName)
exec createtable @dbName = @DbName, @srctblname = @SrcTblName

@DbName and @SrcTblName are the variables that I want to pass to the stored procedure

Any help is greatly appreciated.

c ++ – passing parameters in the C pointer for reference

This is my structure for each node in the stack:

struct Nodo{
int dato;
Nodo *siguiente;
};

I understand how to bind the nodes, but I don't understand why I have to pass the node type parameter, which indicates that it is a pointer * and the & address. This is my working code:

#include 

using namespace std;

struct Nodo{
int dato;
Nodo *siguiente;
};

//Prototipos
void ingresarDatos(Nodo *&tope);
void agregarNodo(Nodo *&nodo, int dato);
void mostrarPila(Nodo *&pila);

int main (){

Nodo *tope=NULL;
ingresarDatos(tope);
mostrarPila(tope);
return 0;

}

//Metodos

void agregarNodo(Nodo *&tope, int dato){

Nodo *nuevoNodo = new Nodo();
nuevoNodo->dato= dato;
nuevoNodo->siguiente=tope;
tope=nuevoNodo;
cout<<"dato actual: "<dato<>dato;
   agregarNodo(tope,dato);

    }
}   

void mostrarPila(Nodo *&tope){

Nodo *aux = tope;
cout<<"** Datos en la PILA **"<dato<siguiente;

    }

}   

What I don't understand is why it doesn't show me the values ​​of my batch when I send it via parameter (node ​​* above) without the & because you have to ask for this parameter, which indicates that it is a pointer and an address.

9.0 pie – problem with passing the URL to the Android Studio app

In my Android app, I try to load an image from the internet and try to create an app called Pokedex.

public class PokemonActivity extends AppCompatActivity {
    private TextView nameTextView;
    private TextView numberTextView;
    private TextView type1TextView;
    private TextView type2TextView;
    private TextView buttonTextView;
    private String imageUrl;
    private ImageView pokemonImage;
    private String url;
    private RequestQueue requestQueue;
    private boolean caught;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_pokemon);

        requestQueue = Volley.newRequestQueue(getApplicationContext());
        url = getIntent().getStringExtra("url");
        nameTextView = findViewById(R.id.pokemon_name);
        numberTextView = findViewById(R.id.pokemon_number);
        type1TextView = findViewById(R.id.pokemon_type1);
        type2TextView = findViewById(R.id.pokemon_type2);
        buttonTextView = findViewById(R.id.button);
        load();

    }

    public void load() {
        type1TextView.setText("");
        type2TextView.setText("");
        JsonObjectRequest request = new JsonObjectRequest(Request.Method.GET, url, null, new Response.Listener() {
            @Override
            public void onResponse(JSONObject response) {
                try {
                    nameTextView.setText(response.getString("name"));

                    numberTextView.setText(String.format("#%03d", response.getInt("id")));
                    JSONObject sprites = response.getJSONObject("sprites");
                    imageUrl = sprites.getString("front_default");
                    JSONArray typeEntries = response.getJSONArray("types");
                    for (int i = 0; i < typeEntries.length(); i++) {
                        JSONObject typeEntry = typeEntries.getJSONObject(i);
                        int slot = typeEntry.getInt("slot");
                        String type = typeEntry.getJSONObject("type").getString("name");

                        if (slot == 1) {
                            type1TextView.setText(type);
                        }
                        else if (slot == 2) {
                            type2TextView.setText(type);
                        }
                    }
                    caught = getPreferences(Context.MODE_PRIVATE).getBoolean(nameTextView.getText().toString(), false);
                    if(caught){
                        buttonTextView.setText("Release");
                    }
                    else{
                        buttonTextView.setText("Catch");
                    }
                } catch (JSONException e) {
                    Log.e("cs50", "Pokemon json error", e);
                }
            }
        }, new Response.ErrorListener() {
            @Override
            public void onErrorResponse(VolleyError error) {
                Log.e("cs50", "Pokemon details error", error);
            }
        });
        requestQueue.add(request);

        new DownloadSpriteTask().execute(imageUrl);
    }

    private class DownloadSpriteTask extends AsyncTask {
        @Override
        protected Bitmap doInBackground(String... strings) {
            try {
                URL url = new URL(strings(0));
                return BitmapFactory.decodeStream(url.openStream());
            } catch (IOException e) {
                Log.e("cs50", "Download sprite error", e);
                return null;
            }
        }

        @Override
        protected void onPostExecute(Bitmap bitmap) {
            // load the bitmap into the ImageView!
            pokemonImage.setImageBitmap(bitmap);
        }
    }
}

However, I get this error:

 java.lang.NullPointerException: Attempt to invoke virtual method 'void android.widget.ImageView.setImageBitmap(android.graphics.Bitmap)' on a null object reference
        at edu.harvard.cs50.pokedex.PokemonActivity$DownloadSpriteTask.onPostExecute(PokemonActivity.java:131)
        at edu.harvard.cs50.pokedex.PokemonActivity$DownloadSpriteTask.onPostExecute(PokemonActivity.java:116)
        at android.os.AsyncTask.finish(AsyncTask.java:695)
        at android.os.AsyncTask.access$600(AsyncTask.java:180)
        at android.os.AsyncTask$InternalHandler.handleMessage(AsyncTask.java:712)
        at android.os.Handler.dispatchMessage(Handler.java:106)
        at android.os.Looper.loop(Looper.java:193)
        at android.app.ActivityThread.main(ActivityThread.java:6669)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)

I can properly extract my imageUrl from the API, but I think that for some reason it is not stored properly in imageUrl.

Please help !!

sql – PROBLEMS WITH PDO when passing parameters

I am trying to use the PDO but have had some difficulties recently.

Before the code appears, a strange fact: if you only pass 1 bindValue, the condition works normally.
If I try to pass more than one parameter, it doesn't work at all.

Let's go to the code:

  include '../conexao_bd/conexao.php';
//ja verifiquei o que chega em cada POST , está tudo ok! vamos trabalhar com valores demonstrativos
$coluna = $_POST('coluna'); // setor
$linha = $_POST('linha'); // 5
$conteudo =$_POST('conteudo'); // blocoA

$conn = getConnection();

$sql = "UPDATE  ?  SET nome_? = ?  WHERE id_? = ? ";

$stmt = $conn->prepare($sql);
$stmt->bindValue(1,$coluna,PDO::PARAM_STR);
$stmt->bindValue(2,$coluna,PDO::PARAM_STR);
$stmt->bindValue(3,$conteudo,PDO::PARAM_STR);
$stmt->bindValue(4,$coluna,PDO::PARAM_STR);
$stmt->bindValue(5,$linha,PDO::PARAM_INT);
$stmt->execute();

I tried to use: it still doesn't work that way

python – Difficulty passing the column in filter_by as a parameter in the Python3 function with SQLAlchemt

I use SQLAlchemy to work with database tables.
I create classes for the corresponding tables, using insert, filter_all () etc. as methods for these classes.

I use the following class with their methods:

class Veiculos(base):
    __tablename__ = "veiculos"

    id = Column(Integer, primary_key=True, autoincrement=True)
    marca =  Column(String(20))
    modelo = Column(String(20))
    ano = Column(Integer)

    def __init__(self, str_marca="None", str_modelo="None", int_ano="None"):
        """
        :param str_marca: (str).
        :param str_modelo: (str).
        :param  int_ano: (int).
        """
        self.marca =  str_marca
        self.modelo = str_modelo
        self.ano = int_ano

    def __repr__(self):
        return "< Veículo {}, {}, {} >".format(self.marca, self.modelo, self.ano)

    def insert(self):
        session.add(self)
        session.commit()

    @classmethod
    def find_by_column(self, session, str_column, var_search):
        search = var_search
        coluna = str_column
        return  session.query(self).filter_by(self.coluna=search).all()

    @classmethod
    def select_all(self, session):        
        return session.query(self)

My problem is in this method:

def find_by_column(self, session, str_column, var_search):
            search = var_search
            coluna = str_column
            return  session.query(self).filter_by(self.coluna=search).all()

Where I want to pass the column name and value to search everything in the function parameters.

What am I doing wrong?

Passing arguments from the URL to the panel page block in Drupal 8

I created a view with a context filter set to a taxonomy term. I am trying to use the view block on a panel page (page manager) and pass the taxonomy term from the URL to the block. I could do this in Drupal 7, but I can't find out in D8.

The page URL is approximately / reports / term1. I want to pass term1 into the block.

For the panel page I have a URL like / reports /! (I also tried% as a placeholder). Although I have seen some recommendations to use a context to adopt the term, I have been unable to do so.

I work with Drupal 8.7.1, Panels 8.x-4.4, Page Manager 8.4-4.0.

Any suggestions on how this can be achieved?

Passing the parameter from the HTML Javascript button to Google Apps Script

I tried this tutorial but it was not written for v8 – and the logger and javascript parameter that is passed between HTML and the script does not work. https://www.youtube.com/watch?v=RRQvySxaCW0
I've learned how to use the older Rhino version of the Google Apps script, but can't pass HTML variables to the script.

In the second part, they give an example of using Javascript to pass the user name to the script.
In the HTML file, I have the following:

var usname = document.getElementById("username").value;
    google.script.run.userClicked(usname);

and in the script I have:

function userClicked(uname){
  Logger.log(uname + " clicked");
}

But I don't get any data in uname. The log says "clicked undefined". Any suggestions to get this going?